[MDEV-4852] Too inefficient query plan for more complex JOIN/SUBQUERY conditions Created: 2013-08-07  Updated: 2013-08-21  Resolved: 2013-08-21

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.32
Fix Version/s: 5.5.33

Type: Bug Priority: Major
Reporter: stark skalle Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: optimizer
Environment:

Windows 7 64bit, PHP 5.3.8

Attachments: PNG File Explain-MariaDB.png     PNG File Explain-MySql.png     File test.sql    

 Description   

SQL:

SELECT `i`.`id_inzerat`, `ud_boss`.`id_udalost`, `ud_boss`.`id_udalost_akce` AS `boss_akce`, `ud_makler`.`id_udalost`, `ud_makler`.`id_udalost_akce` AS `makler_akce`
 
FROM `inzerat` AS `i`
 
LEFT JOIN `detail` AS `d` ON d.id_detail = i.id_detail
 
LEFT JOIN `nabidka` AS `n` ON n.id_nabidka = d.id_nabidka
 
LEFT JOIN `udalost` AS `ud_boss` ON (ud_boss.id_nabidka = n.id_nabidka OR ud_boss.id_inzerat = i.id_inzerat) AND ud_boss.hotovo = 0 AND ud_boss.id_udalost_akce IN (18)
 
LEFT JOIN `udalost` AS `ud_makler` ON (ud_makler.id_nabidka = n.id_nabidka OR ud_makler.id_inzerat = i.id_inzerat) AND ud_makler.hotovo = 0 AND ud_makler.id_udalost_akce IN (4,5,8,9,10)
 
WHERE (i.id_stav IN (1, 2, 6)) AND (i.public = 1)
 
GROUP BY `i`.`id_inzerat`
 
ORDER BY `i`.`datum` DESC

There is huge performance difference between MySQL (>1s) and MariaDB (>60s). The problem is in here "ud_makler.id_udalost_akce IN (4,5,8,9,10)" - if changed to for example "ud_makler.id_udalost_akce IN (4)" than the query has roughly same speed as MySQL. Including explains fot the query above for MySQL (5.5.16) and MariaDB.

I hope this was not reported yet...

 Comments   
Comment by Igor Babaev [ 2013-08-07 ]

Could you provide a test case for this problem?

Thanks,
Igor.

Comment by stark skalle [ 2013-08-07 ]

Hi Igor,

I am new to MariaDB JIRA - what should a test case involve?

Thanks

Comment by Igor Babaev [ 2013-08-07 ]

Just an instruction/description of how the problem could be reproduced..

Comment by stark skalle [ 2013-08-07 ]

I have added database dump - run the query from the description to replicate the issue

Comment by Elena Stepanova [ 2013-08-08 ]

Reproducible with the attached data (just execute test.sql and then the query from the description).

On current MariaDB 5.5:
241 rows in set (1 min 46.41 sec)

On current MySQL 5.5:
241 rows in set (0.21 sec)

Comment by Patryk Pomykalski [ 2013-08-08 ]

You can add index hint as a workaround:
LEFT JOIN `udalost` AS `ud_makler` USE INDEX (`ALL`)

Comment by Igor Babaev [ 2013-08-14 ]

This problem is also seen in mysql-5.6.13:
mysql> EXPLAIN SELECT `i`.`id_inzerat`, `ud_boss`.`id_udalost`, `ud_boss`.`id_udalost_akce` AS `boss_akce`, `ud_makler`.`id_udalost`, `ud_makler`.`id_udalost_akce` AS `makler_akce` FROM `inzerat` AS `i` LEFT JOIN `detail` AS `d` ON d.id_detail = i.id_detail LEFT JOIN `nabidka` AS `n` ON n.id_nabidka = d.id_nabidka LEFT JOIN `udalost` AS `ud_boss` ON (ud_boss.id_nabidka = n.id_nabidka OR ud_boss.id_inzerat = i.id_inzerat) AND ud_boss.hotovo = 0 AND ud_boss.id_udalost_akce IN (18) LEFT JOIN `udalost` AS `ud_makler` ON (ud_makler.id_nabidka = n.id_nabidka OR ud_makler.id_inzerat = i.id_inzerat) AND ud_makler.hotovo = 0 AND ud_makler.id_udalost_akce IN (4,5,8,9,10) WHERE (i.id_stav IN (1, 2, 6)) AND (i.public = 1) GROUP BY `i`.`id_inzerat` ORDER BY `i`.`datum` DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 SIMPLE i range PRIMARY,mixed,id_stav,public,search_offer mixed 4 NULL 323 Using index condition; Using temporary; Using filesort
1 SIMPLE d eq_ref PRIMARY PRIMARY 4 rkokno.i.id_detail 1 NULL
1 SIMPLE n eq_ref PRIMARY PRIMARY 4 rkokno.d.id_nabidka 1 Using index
1 SIMPLE ud_boss ref id_akce,hotovo,id_nabidka,id_inzerat,ALL,search_offer ALL 5 const,const 16 Using where
1 SIMPLE ud_makler range id_nabidka,id_inzerat,search_offer ALL 5 NULL 19 Range checked for each record (index map: 0x58)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Comment by Igor Babaev [ 2013-08-14 ]

It looks like this a manifestation of a more general problem that affects all versions of MariaDB/MySQL
(see mdev-4894 and http://bugs.mysql.com/bug.php?id=70021)

Comment by Igor Babaev [ 2013-08-21 ]

This problems was fixed by the patch for bug mdev-4894 that has been pushed into the 5.1 tree and later merged into the 5.2, 5.3, 5.5 trees.

Generated at Thu Feb 08 06:59:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.