Why MySQL database index does not help improve performance? And how to use New Relic to find out the cause.
I went through some of the sql tutorial on how to create index in MySQL. It took very little time for me to create my first MySQL index, and job done. That’s what I thought…
After 1 month launching the API, we decide to integrate it with existing sites.
Table A starts from less than 500 rows to 370000++ rows after importing all data from different sites. Immediately the response time of any functions that SELECT from the table can go up to 3000 ms (that’s almost 3 seconds).
After created the index, nothing seems to improve, after observing New Relic for sometime, it showing analysis of index using “PRIMARY”. The query is not using the newly created index.
New Relic is showing very awful and horrible average response time!!
Thanks to some tips from a MySQL guru, it seems that MySQL will auto decide which index to use to execute your query if you didn’t specify any.
By using MySQL’s “Index Hint Syntax“, I’m now able to force the sql query to execute with the specified index.
SELECT * FROM table1 USE INDEX (indexname1,indexname2) WHERE col1=1 AND col2=2 AND col3=3
Ran the same query on MySQL Workbench and immediately i got results with huge performance differences.
After the deployment of the new code by adding “USE INDEX“, New Relic is showing some nice average response time. NO MORE 3000 ms response time!!
Even though there is huge performance improvement, but I feel that there is room for improvement. After all, New Relic suggest that i can do more. Here is what New Relic suggest.