![]() ![]() The table will continue to grow to a much larger size (eventually tens of millions). ![]() There are 95,600 rows in the movies table. | 1 | SIMPLE | m | ref | ind_s_id | ind_s_id | 4 | const | 39941 | NULL | #GROUP BY m.mg_id, CASE WHEN m.mg_id IS NULL THEN m.id ELSE 0 END To help MySQL optimize queries better, use ANALYZE TABLE or run myisamchk -analyze on a table after it has been loaded with data. SELECT SQL_NO_CACHE m.id, m.sku, m.movie_url Without the GROUP BY statement, the above times drop further. Much better, but it still is a pretty slow query.ĬASE 3 - Without GROUP BY and without CASE | 1 | SIMPLE | m | index | ind_mg_id,ind_s_id | ind_mg_id | 9 | NULL | 2200 | Using where | GROUP BY m.mg_id#, CASE WHEN m.mg_id IS NULL THEN m.id ELSE 0 END I realized the CASE statement in the GROUP BY might slow things down, so I tried removing it for testing purposes (it has to be there for production use, though): SELECT SQL_NO_CACHE m.id, m.sku, m.movie_url | 1 | SIMPLE | m | ref | ind_mg_id,ind_s_id | ind_s_id | 4 | const | 39941 | Using where Using temporary Using filesort | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | GROUP BY m.mg_id, CASE WHEN m.mg_id IS NULL THEN m.id ELSE 0 ENDĮxplain output: +-+-+-+-+-+-+-+-+-+-+ To optimize the above query, we can just add an index to the ‘ customerid ‘ field using the below syntax: mysql> Create index customerid ON customers (customerId) Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0. I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |