9/10/2023 0 Comments Mysql speed up query![]() 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 SELECT SQL_NO_CACHE m.id, m.sku, m.movie_url Without the GROUP BY statement, the above times drop further. I want to speed up the following query as i add 7 million entries to dsldata table every month and now after 6 months it takes too much time to execute, currently i move data to another table after every few months to speed up this query but i am looking for other ways for it, i was thinking to use partitioning on dsldata. 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 |