[MDEV-6407] Incredibly slow performance on SELECT query (and its corresponding EXPLAIN), works fine on MySQL - JIRA

Details

Type: Bug
Status: In Progress
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.3.12, 5.5.38, 10.0.12, 10.1.0
Fix Version/s: 10.1, 10.0
Component/s: None
Labels:
- optimizer
Environment:
opensuse 13.1, kernel 3.11.10 on x86, SSD drive, 2GB RAM

Sprint:
10.1.6-2, 10.0.21

Description

With a very, very small dataset (less than 1KB of actual data), a SELECT query takes several minutes. The EXPLAIN query takes just as long, leading me to believe it is an optimiser bug. On MySQL 5.5.33 it [the EXPLAIN query] takes 0.00s. On MariaDB 5.5.33 it takes an eternity (also tried on latest stable 5.x and 10.x versions, 5.5.38 and 10.0.12 at the time of writing). This is with the highly reduced testcase I'm including. With my actual dataset (still quite small less than 1MB in total table sizes according to phpMyAdmin), I killed the EXPLAIN query after 6 HOURS!

Admittedly, the query has a redundant join in it, which I removed and it fixed the problem I was experiencing, but I think this is a bug anyway.

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

bug.sql
2 kB
2014-06-30 06:16

Issue Links

is duplicated by

MDEV-6724 Slow query optimizer with certain subqueries

Open

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-06-30 09:57 - edited

Hi Steven,

Thanks for the report.

EXPLAIN on current MariaDB 5.5:

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                                            |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary                                     |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                      |
|    1 | PRIMARY     | b     | index  | PRIMARY       | PRIMARY | 4       | NULL        |    2 |   100.00 | Using index; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (incremental, BNL join)           |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using index                                                      |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                      |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                      |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (incremental, BNL join)           |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (incremental, BNL join)           |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                      |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                      |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 |                                                                  |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.b_id |    1 |   100.00 | Using index                                                      |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; End temporary; Using join buffer (flat, BNL join)   |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+
25 rows in set, 1 warning (1 min 29.09 sec)

| Note  | 1003 | select `test`.`a`.`id` AS `id` from `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) join `test`.`a` where ((`test`.`c`.`val` = 'c1') and (`test`.`d`.`val` = 'd1') and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`c`.`val` = 'c2') and (`test`.`d`.`val` = 'd2') and (`test`.`c`.`val` = 'c3') and (`test`.`d`.`val` = 'd3') and (`test`.`c`.`val` = 'c4') and (`test`.`d`.`val` = 'd4') and (`test`.`c`.`val` = 'c5') and (`test`.`d`.`val` = 'd5') and (`test`.`c`.`val` = 'c6') and (`test`.`d`.`val` = 'd6') and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`c`.`b_id` = `test`.`b`.`id`) and (`test`.`a`.`b_id` = `test`.`b`.`id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`c`.`b_id` = `test`.`a`.`b_id`) and (`test`.`b`.`id` = `test`.`a`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`)) |

EXPLAIN on MySQL 5.6:

+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                                             |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Start temporary                                      |
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   100.00 | Using index                                                       |
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using index                                                       |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   100.00 | Using where                                                       |
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; Using join buffer (Block Nested Loop)                |
|  1 | SIMPLE      | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   100.00 | Using where; End temporary; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+
25 rows in set, 1 warning (0.06 sec)

| Note  | 1003 | /* select#1 */ select `test`.`a`.`id` AS `id` from `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) join `test`.`a` where ((`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`d`.`val` = 'd6') and (`test`.`c`.`val` = 'c6') and (`test`.`d`.`val` = 'd5') and (`test`.`c`.`val` = 'c5') and (`test`.`d`.`val` = 'd4') and (`test`.`c`.`val` = 'c4') and (`test`.`d`.`val` = 'd3') and (`test`.`c`.`val` = 'c3') and (`test`.`d`.`val` = 'd2') and (`test`.`c`.`val` = 'c2') and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`val` = 'd1') and (`test`.`c`.`val` = 'c1')) |

ANALYZE on current 10.1:

+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | r_rows | filtered | r_filtered | Extra                                                            |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |      1 |   100.00 |       0.00 | Using where; Start temporary                                     |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
|    1 | PRIMARY     | b     | index  | PRIMARY       | PRIMARY | 4       | NULL        |    2 |   NULL |   100.00 |       NULL | Using index; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (incremental, BNL join)           |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Using join buffer (incremental, BNL join)           |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (incremental, BNL join)           |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where                                                      |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL | Using where                                                      |
|    1 | PRIMARY     | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.d.a_id |    1 |   NULL |   100.00 |       NULL |                                                                  |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; Using join buffer (flat, BNL join)                  |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.c.b_id |    1 |   NULL |   100.00 |       NULL | Using index; End temporary                                       |
|    1 | PRIMARY     | c     | ALL    | PRIMARY       | NULL    | NULL    | NULL        |    1 |   NULL |   100.00 |       NULL | Using where; Start temporary; Using join buffer (flat, BNL join) |
|    1 | PRIMARY     | b     | eq_ref | PRIMARY       | PRIMARY | 4       | test.a.b_id |    1 |   NULL |   100.00 |       NULL | Using index                                                      |
|    1 | PRIMARY     | d     | ALL    | NULL          | NULL    | NULL    | NULL        |   10 |   NULL |   100.00 |       NULL | Using where; End temporary; Using join buffer (flat, BNL join)   |
+------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+
25 rows in set (1 min 32.09 sec)

Show

Elena Stepanova added a comment - 2014-06-30 09:57 - edited Hi Steven, Thanks for the report. EXPLAIN on current MariaDB 5.5: +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+ | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Start temporary | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | PRIMARY | b | index | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using index; Using join buffer (flat, BNL join) | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using index | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where ; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (flat, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where ; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (flat, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where ; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_id | 1 | 100.00 | Using index | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; End temporary; Using join buffer (flat, BNL join) | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+------------------------------------------------------------------+ 25 rows in set, 1 warning (1 min 29.09 sec) | Note | 1003 | select `test`.`a`.`id` AS `id` from `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) join `test`.`a` where ((`test`.`c`.`val` = 'c1') and (`test`.`d`.`val` = 'd1') and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`c`.`val` = 'c2') and (`test`.`d`.`val` = 'd2') and (`test`.`c`.`val` = 'c3') and (`test`.`d`.`val` = 'd3') and (`test`.`c`.`val` = 'c4') and (`test`.`d`.`val` = 'd4') and (`test`.`c`.`val` = 'c5') and (`test`.`d`.`val` = 'd5') and (`test`.`c`.`val` = 'c6') and (`test`.`d`.`val` = 'd6') and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`c`.`b_id` = `test`.`b`.`id`) and (`test`.`a`.`b_id` = `test`.`b`.`id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`c`.`b_id` = `test`.`a`.`b_id`) and (`test`.`b`.`id` = `test`.`a`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`)) | EXPLAIN on MySQL 5.6: +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+ | 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Start temporary | | 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | 100.00 | Using index | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using index | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | 100.00 | Using where | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where ; End temporary; Using join buffer (Block Nested Loop) | +----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------------------------------------------------------------+ 25 rows in set, 1 warning (0.06 sec) | Note | 1003 | /* select #1 */ select `test`.`a`.`id` AS `id` from `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` join `test`.`a` join `test`.`b` semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) semi join (`test`.`c` join `test`.`d`) join `test`.`a` where ((`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`b`.`id` = `test`.`c`.`b_id`) and (`test`.`a`.`b_id` = `test`.`c`.`b_id`) and (`test`.`d`.`c_id` = `test`.`c`.`id`) and (`test`.`d`.`val` = 'd6') and (`test`.`c`.`val` = 'c6') and (`test`.`d`.`val` = 'd5') and (`test`.`c`.`val` = 'c5') and (`test`.`d`.`val` = 'd4') and (`test`.`c`.`val` = 'c4') and (`test`.`d`.`val` = 'd3') and (`test`.`c`.`val` = 'c3') and (`test`.`d`.`val` = 'd2') and (`test`.`c`.`val` = 'c2') and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`a`.`id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`a_id` = `test`.`d`.`a_id`) and (`test`.`d`.`val` = 'd1') and (`test`.`c`.`val` = 'c1')) | ANALYZE on current 10.1: +------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+ | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | 1 | 100.00 | 0.00 | Using where ; Start temporary | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | NULL | 100.00 | NULL | Using index | | 1 | PRIMARY | b | index | PRIMARY | PRIMARY | 4 | NULL | 2 | NULL | 100.00 | NULL | Using index; Using join buffer (flat, BNL join) | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | Using where ; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | NULL | 100.00 | NULL | Using index | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | NULL | 100.00 | NULL | Using where ; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | NULL | 100.00 | NULL | Using index | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | Using where ; Using join buffer (flat, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | NULL | 100.00 | NULL | Using where ; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | NULL | 100.00 | NULL | Using index | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | Using where ; Using join buffer (flat, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | NULL | 100.00 | NULL | Using where ; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | Using where ; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | Using where ; Using join buffer (incremental, BNL join) | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | NULL | 100.00 | NULL | Using where | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | NULL | 100.00 | NULL | Using where | | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | test.d.a_id | 1 | NULL | 100.00 | NULL | | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | Using where ; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.c.b_id | 1 | NULL | 100.00 | NULL | Using index; End temporary | | 1 | PRIMARY | c | ALL | PRIMARY | NULL | NULL | NULL | 1 | NULL | 100.00 | NULL | Using where ; Start temporary; Using join buffer (flat, BNL join) | | 1 | PRIMARY | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.b_id | 1 | NULL | 100.00 | NULL | Using index | | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 10 | NULL | 100.00 | NULL | Using where ; End temporary; Using join buffer (flat, BNL join) | +------+-------------+-------+--------+---------------+---------+---------+-------------+------+--------+----------+------------+------------------------------------------------------------------+ 25 rows in set (1 min 32.09 sec)

Hide

Permalink

Steven Don added a comment - 2014-06-30 12:03

Runtime seems to be exponentially increasing dependent on the number of subqueries (even though they are independent) and 2 records in table B seems to be the sweet (or sour) spot.

The original dataset had covering indexes that could be used for all the joins (and both MySQL and MariaDB used them in the cases where I waited long enough to get output), but that didn't make any difference to the performance of the EXPLAIN query or even to the SELECT, given the small amount of data, so I left them out of the testcase.

SHOW PROCESSLIST just tells me it's in the "statistics" phase, burning 100% CPU.

Show

Steven Don added a comment - 2014-06-30 12:03 Runtime seems to be exponentially increasing dependent on the number of subqueries (even though they are independent) and 2 records in table B seems to be the sweet (or sour) spot. The original dataset had covering indexes that could be used for all the joins (and both MySQL and MariaDB used them in the cases where I waited long enough to get output), but that didn't make any difference to the performance of the EXPLAIN query or even to the SELECT, given the small amount of data, so I left them out of the testcase. SHOW PROCESSLIST just tells me it's in the "statistics" phase, burning 100% CPU.

Hide

Permalink

Sergei Petrunia added a comment - 2014-07-14 16:55

if I set

optimizer_search_depth=1

then EXPLAIN finishes in 0.01 sec. The generated plan is different (not sure if it's better or worse). It seems, the time is spent in the join optimizer.

Show

Sergei Petrunia added a comment - 2014-07-14 16:55 if I set optimizer_search_depth=1 then EXPLAIN finishes in 0.01 sec. The generated plan is different (not sure if it's better or worse). It seems, the time is spent in the join optimizer.

Hide

Permalink

Sergei Petrunia added a comment - 2014-07-14 16:56

Checked how many possible join orders are considered by the optimizer. The
number is huge:

105,099,658 /tmp/trace1

(If you count tables inside semi-joins, the join has 25 tables)

If I change all of the subqueries to be joins, then EXPLAIN finishes in 0.01 sec.

1167 /tmp/trace1

The number of considered join combinations is much smaller.

It seems, something is wrong with join plan pruning when semi-joins are present.

Show

Sergei Petrunia added a comment - 2014-07-14 16:56 Checked how many possible join orders are considered by the optimizer. The number is huge: 105,099,658 /tmp/trace1 (If you count tables inside semi-joins, the join has 25 tables) If I change all of the subqueries to be joins, then EXPLAIN finishes in 0.01 sec. 1167 /tmp/trace1 The number of considered join combinations is much smaller. It seems, something is wrong with join plan pruning when semi-joins are present.

Hide

Permalink

Sergei Petrunia added a comment - 2014-07-18 13:48

the default value for @@optimizer_search_depth is 62. That is, the optimizer performs exhaustive plan search, the branches are cut off via pruning.

If I set optimizer_search_depth=0 (which means "set depth automatically), I get:

time to run EXPLAIN (debug build, lenovo edge e130):
join - 0.02 sec
subqueries -0.10 sec.

determine_search_depth()=7 for both cases.

Show

Sergei Petrunia added a comment - 2014-07-18 13:48 the default value for @@optimizer_search_depth is 62. That is, the optimizer performs exhaustive plan search, the branches are cut off via pruning. If I set optimizer_search_depth=0 (which means "set depth automatically), I get: time to run EXPLAIN (debug build, lenovo edge e130): join - 0.02 sec subqueries -0.10 sec. determine_search_depth()=7 for both cases.

Hide

Permalink

Sergei Golubchik added a comment - 2014-07-22 17:10

This won't be fixed in 5.5, because a fix can change many existing execution plans and we cannot do that in 5.5 anymore. But there is a simple workaround for this bug (set optimizer_search_depth to a smaller value).

Show

Sergei Golubchik added a comment - 2014-07-22 17:10 This won't be fixed in 5.5, because a fix can change many existing execution plans and we cannot do that in 5.5 anymore. But there is a simple workaround for this bug (set optimizer_search_depth to a smaller value).

Incredibly slow performance on SELECT query (and its corresponding EXPLAIN), works fine on MySQL

Details

Description

Gliffy Diagrams

Attachments

Attachments

Issue Links

Activity

People

Dates

Agile