Here it is, the command was: RESET QUERY CACHE; FLUSH STATUS; SELECT b.*, p.category_id FROM brands b JOIN products p USING(brand_id) GROUP BY category_id, brand_id ORDER BY title ASC; SHOW STATUS LIKE 'Handler%'; 5.5 --- +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_icp_attempts | 0 | | Handler_icp_match | 0 | | Handler_mrr_init | 0 | | Handler_mrr_key_refills | 0 | | Handler_mrr_rowid_refills | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 11051 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 190 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 62730 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_tmp_update | 0 | | Handler_tmp_write | 62508 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 10.0 ---- +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 0 | | Handler_icp_attempts | 0 | | Handler_icp_match | 0 | | Handler_mrr_init | 0 | | Handler_mrr_key_refills | 0 | | Handler_mrr_rowid_refills | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 150 | | Handler_read_last | 0 | | Handler_read_next | 62206 | | Handler_read_prev | 0 | | Handler_read_rnd | 187 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 339 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_tmp_update | 0 | | Handler_tmp_write | 62206 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ I already tried to remove order by and also group by but it didn't make any difference related to speed. Citát Sergey Petrunia <sergey@mariadb.com>:
On Thu, Feb 11, 2016 at 03:43:54PM +0100, azurit@pobox.sk wrote:
Hi,
we upgraded MariaDB 5.5 to 10.0 on one server last night and are now dealing with severe performance issues. Here is one case where it's notable:
Query - SELECT b.*, p.category_id FROM brands b JOIN products p USING(brand_id) GROUP BY category_id, brand_id ORDER BY title ASC;
...
Query time on 5.5: about 0.2s Query time on 10.0: between 2 and 9s (if not cached)
Explain on 5.5: http://watchdog.sk/5.5.png Explain on 10.0: http://watchdog.sk/10.png
This is weird, query plans look the same. However, p.rows = 376, which means that on version 10.0 the optimizer expects to read a lot more rows.
Can you run the queries again (on both 5.5 and 10.0) and check how many rows they actually read? This can be done as follows:
FLUSH STATUS; <query>; SHOW STATUS LIKE 'Handler%';
I would also try removing the ORDER BY clause and see if the difference in query execution time is still there.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog