On Thu, Feb 11, 2016 at 03:43:54PM +0100, azu...@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 _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
