Details
Major
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...
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi Igor,
I am new to MariaDB JIRA - what should a test case involve?
Thanks
Just an instruction/description of how the problem could be reproduced..
I have added database dump - run the query from the description to replicate the issue
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)
You can add index hint as a workaround:
LEFT JOIN `udalost` AS `ud_makler` USE INDEX (`ALL`)
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) |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
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)
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.
Could you provide a test case for this problem?
Thanks,
Igor.