Improve slow query large table with millions of rows. Its common issue – slow query in large table. Here are cases for performance optimization.
When MySQL table have millions of rows and require process on it, select query optimization is must.
Sometimes basic query optimization like indexing, query pattern etc., not helpful on big tables. Which have millions of rows.
Here i would like to share special case in which after trying many changes not able to speed up select query output. At last by making small change i got nice improvement in slow query.
Slow query large table case
I have table with 30 million records and it increasing day by day. When i create page for prepare statistics on data, page load very slow and sometime it timeout and shows blank page.
Below is my select query which is slow on large table.
SELECT * FROM table WHERE status="A" AND datecreate > "YYYY-MM-DD" ORDER BY id_field DESC LIMIT 0, 30
Solution 1 for slow query
For solution i used INDEX on where condition datecreate field. It gives speed for specific time. You can find more detail for MySQL indexing here.
After few months when data increase in table, MySQL server query became again slow and query timeout issue started.
Solution 2 for slow query
Now i search for another solution for slow query in very large table. After tries many solutions, not get working solution. Finally decide to move older entry to other backup table.
But before move data i try one more time for speed up query. I had analyzed select query and make some changes in it and prepare below updated query which gives rocket speed in select query performance.
SELECT * FROM table WHERE status="A" AND datecreate > "YYYY-MM-DD" ORDER BY datecreate DESC LIMIT 0, 30
Right, using where condition field (datecreate) in order by gives more speed compare to primary key field (id_field).
When i check both query using DESCRIBE first one looks more optimized and it process less rows compare to second query, but in actual use second query gives me more speed and fast output.
Hope above case and change process helpful for optimize your slow sql query on large table.