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