maria-discuss team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

On Thu, Feb 11, 2016 at 03:43:54PM +0100, azurit@xxxxxxxx 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