-1

I encountered an issue today and I'm not sure if this is a bug in MySQL 8 or if I'm doing something wrong.

Preface

At the bottom of the post I attached a query to reproduce the issue locally.

I have three tables: clients and two more. All tables have ~50 columns. I'm making a very simple query that joins all tables together to the clients table by a foreign key and then just orders them.

The clients table has 1M rows. The two other tables are empty.

The problematic query looks as follows:

select
    test_client.*,
    test_join1.*,
    test_join2.*
from
    test_client
    left join test_join1 on test_client.id = test_join1.client_id
    left join test_join2 on test_client.id = test_join2.client_id
order by test_client.id
limit 10;

This query takes 4-5 seconds to complete.

When I remove ORDER BY it executed in 50 ms. If I disable hash joins by using the /*+ NO_BNL() */ planner hint it also completes in 50 ms.

Here's the query plans:

1. Regular query with ORDER_BY:

EXPLAIN ANALYZE:

-> Limit: 10 row(s)  (actual time=4757..4757 rows=10 loops=1)
    -> Sort: test_client.id, limit input to 10 row(s) per chunk  (actual time=4757..4757 rows=10 loops=1)
        -> Stream results  (cost=194554 rows=994944) (actual time=0.59..4241 rows=1e+6 loops=1)
            -> Left hash join (test_join2.client_id = test_client.id)  (cost=194554 rows=994944) (actual time=0.574..1334 rows=1e+6 loops=1)
                -> Left hash join (test_join1.client_id = test_client.id)  (cost=147022 rows=994944) (actual time=0.444..1240 rows=1e+6 loops=1)
                    -> Table scan on test_client  (cost=100801 rows=994944) (actual time=0.331..1118 rows=1e+6 loops=1)
                    -> Hash
                        -> Table scan on test_join1  (cost=0.0478 rows=1) (actual time=0.0984..0.0984 rows=0 loops=1)
                -> Hash
                    -> Table scan on test_join2  (cost=0.0955 rows=1) (actual time=0.117..0.117 rows=0 loops=1)

Execution plan for query with order by

EXPLAIN:

+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|id|select_type|table      |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra                                     |
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|1 |SIMPLE     |test_client|null      |ALL |null         |null|null   |null|994944|100     |Using temporary; Using filesort           |
|1 |SIMPLE     |test_join1 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
|1 |SIMPLE     |test_join2 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+

2. Without ORDER BY:

EXPLAIN ANALYZE:

-> Limit: 10 row(s)  (cost=194554 rows=10) (actual time=0.0813..0.096 rows=10 loops=1)
    -> Left hash join (test_join2.client_id = test_client.id)  (cost=194554 rows=994944) (actual time=0.0806..0.0946 rows=10 loops=1)
        -> Left hash join (test_join1.client_id = test_client.id)  (cost=147022 rows=994944) (actual time=0.0643..0.0776 rows=10 loops=1)
            -> Table scan on test_client  (cost=100801 rows=994944) (actual time=0.0482..0.0607 rows=10 loops=1)
            -> Hash
                -> Table scan on test_join1  (cost=0.0478 rows=1) (actual time=0.00996..0.00996 rows=0 loops=1)
        -> Hash
            -> Table scan on test_join2  (cost=0.0955 rows=1) (actual time=0.0105..0.0105 rows=0 loops=1)

Execution plan for query without order by

EXPLAIN:

+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|id|select_type|table      |partitions|type|possible_keys|key |key_len|ref |rows  |filtered|Extra                                     |
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+
|1 |SIMPLE     |test_client|null      |ALL |null         |null|null   |null|994944|100     |null                                      |
|1 |SIMPLE     |test_join1 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
|1 |SIMPLE     |test_join2 |null      |ALL |client_id    |null|null   |null|1     |100     |Using where; Using join buffer (hash join)|
+--+-----------+-----------+----------+----+-------------+----+-------+----+------+--------+------------------------------------------+

3. With /*+ NO_BNL() */ hint: EXPLAIN ANALYZE:

-> Limit: 10 row(s)  (cost=497474 rows=10) (actual time=0.179..0.3 rows=10 loops=1)
    -> Nested loop left join  (cost=497474 rows=10) (actual time=0.178..0.296 rows=10 loops=1)
        -> Nested loop left join  (cost=248737 rows=10) (actual time=0.144..0.239 rows=10 loops=1)
            -> Index scan on test_client using PRIMARY  (cost=0.0131 rows=10) (actual time=0.0782..0.148 rows=10 loops=1)
            -> Index lookup on test_join1 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.00824..0.00824 rows=0 loops=10)
        -> Index lookup on test_join2 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.0048..0.0048 rows=0 loops=10)

Explain plan with hash joins disabled

EXPLAIN:

+--+-----------+-----------+----------+-----+-------------+---------+-------+----------------------------+----+--------+-----+
|id|select_type|table      |partitions|type |possible_keys|key      |key_len|ref                         |rows|filtered|Extra|
+--+-----------+-----------+----------+-----+-------------+---------+-------+----------------------------+----+--------+-----+
|1 |SIMPLE     |test_client|null      |index|null         |PRIMARY  |4      |null                        |10  |100     |null |
|1 |SIMPLE     |test_join1 |null      |ref  |client_id    |client_id|5      |hashjoinissue.test_client.id|1   |100     |null |
|1 |SIMPLE     |test_join2 |null      |ref  |client_id    |client_id|5      |hashjoinissue.test_client.id|1   |100     |null |
+--+-----------+-----------+----------+-----+-------------+---------+-------+----------------------------+----+--------+-----+

Is this a bug in mysql 8, or am I doing something wrong?


EDIT 1

Just to clarify - the real table doesn't look exactly like this one here, but it has a similar amount of columns (on average).

The real dataset also does have some data in test_join1 and test_join2 tables - I just realized that the amount of data there does not seem to matter all that much.

I added so many columns to illustrate the problem. What I did notice however is that if the query does not ask for all of the columns, but for example only asks for ids:

select
    test_client.id,
    test_join1.id,
    test_join2.id
from
    test_client
    left join test_join1 on test_client.id = test_join1.client_id
    left join test_join2 on test_client.id = test_join2.client_id
order by test_client.id limit 10;

Then the query is almost instant. Note that none of the other columns is used in any further filtering, joining or ordering, yet when the planner expects the resulting data to be large enough - it switches to using a hash join which is not the most optimal solution.

EXPLAIN:

+--+-----------+-----------+----------+-----+-------------+--------------+-------+----------------------------+----+--------+-----------+
|id|select_type|table      |partitions|type |possible_keys|key           |key_len|ref                         |rows|filtered|Extra      |
+--+-----------+-----------+----------+-----+-------------+--------------+-------+----------------------------+----+--------+-----------+
|1 |SIMPLE     |test_client|null      |index|null         |test_client_id|4      |null                        |10  |100     |Using index|
|1 |SIMPLE     |test_join1 |null      |ref  |client_id    |client_id     |5      |hashjoinissue.test_client.id|1   |100     |Using index|
|1 |SIMPLE     |test_join2 |null      |ref  |client_id    |client_id     |5      |hashjoinissue.test_client.id|1   |100     |Using index|
+--+-----------+-----------+----------+-----+-------------+--------------+-------+----------------------------+----+--------+-----------+

EXPLAIN ANALYZE:

-> Limit: 10 row(s)  (cost=497474 rows=10) (actual time=0.796..0.835 rows=10 loops=1)
    -> Nested loop left join  (cost=497474 rows=10) (actual time=0.794..0.833 rows=10 loops=1)
        -> Nested loop left join  (cost=248737 rows=10) (actual time=0.721..0.757 rows=10 loops=1)
            -> Covering index scan on test_client using test_client_id  (cost=0.0131 rows=10) (actual time=0.543..0.545 rows=10 loops=1)
            -> Covering index lookup on test_join1 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.0198..0.0198 rows=0 loops=10)
        -> Covering index lookup on test_join2 using client_id (client_id=test_client.id)  (cost=0.25 rows=1) (actual time=0.00645..0.00645 rows=0 loops=10)

Explain analyze when asking for just IDs


Query to reproduce data

The following query can be used to reproduce the data locally:

drop table if exists test_join1;
drop table if exists test_join2;
drop table if exists test_client cascade;

create table if not exists test_client (
    id int auto_increment primary key,
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255),
    col7 varchar(255),
    col8 varchar(255),
    col9 varchar(255),
    col10 varchar(255),
    col11 varchar(255),
    col12 varchar(255),
    col13 varchar(255),
    col14 varchar(255),
    col15 varchar(255),
    col16 varchar(255),
    col17 varchar(255),
    col18 varchar(255),
    col19 varchar(255),
    col20 varchar(255),
    col21 varchar(255),
    col22 varchar(255),
    col23 varchar(255),
    col24 varchar(255),
    col25 varchar(255),
    col26 varchar(255),
    col27 varchar(255),
    col28 varchar(255),
    col29 varchar(255),
    col30 varchar(255),
    col31 varchar(255),
    col32 varchar(255),
    col33 varchar(255),
    col34 varchar(255),
    col35 varchar(255),
    col36 varchar(255),
    col37 varchar(255),
    col38 varchar(255),
    col39 varchar(255),
    col40 varchar(255),
    col41 varchar(255),
    col42 varchar(255),
    col43 varchar(255),
    col44 varchar(255),
    col45 varchar(255),
    col46 varchar(255),
    col47 varchar(255),
    col48 varchar(255),
    col49 varchar(255)
);
create table if not exists test_join1 (
    id int auto_increment primary key,
    client_id int,
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255),
    col7 varchar(255),
    col8 varchar(255),
    col9 varchar(255),
    col10 varchar(255),
    col11 varchar(255),
    col12 varchar(255),
    col13 varchar(255),
    col14 varchar(255),
    col15 varchar(255),
    col16 varchar(255),
    col17 varchar(255),
    col18 varchar(255),
    col19 varchar(255),
    col20 varchar(255),
    col21 varchar(255),
    col22 varchar(255),
    col23 varchar(255),
    col24 varchar(255),
    col25 varchar(255),
    col26 varchar(255),
    col27 varchar(255),
    col28 varchar(255),
    col29 varchar(255),
    col30 varchar(255),
    col31 varchar(255),
    col32 varchar(255),
    col33 varchar(255),
    col34 varchar(255),
    col35 varchar(255),
    col36 varchar(255),
    col37 varchar(255),
    col38 varchar(255),
    col39 varchar(255),
    col40 varchar(255),
    col41 varchar(255),
    col42 varchar(255),
    col43 varchar(255),
    col44 varchar(255),
    col45 varchar(255),
    col46 varchar(255),
    col47 varchar(255),
    col48 varchar(255),
    col49 varchar(255),
    foreign key (client_id) references test_client(id)
);
create table if not exists test_join2 (
    id int auto_increment primary key,
    client_id int,
    col1 varchar(255),
    col2 varchar(255),
    col3 varchar(255),
    col4 varchar(255),
    col5 varchar(255),
    col6 varchar(255),
    col7 varchar(255),
    col8 varchar(255),
    col9 varchar(255),
    col10 varchar(255),
    col11 varchar(255),
    col12 varchar(255),
    col13 varchar(255),
    col14 varchar(255),
    col15 varchar(255),
    col16 varchar(255),
    col17 varchar(255),
    col18 varchar(255),
    col19 varchar(255),
    col20 varchar(255),
    col21 varchar(255),
    col22 varchar(255),
    col23 varchar(255),
    col24 varchar(255),
    col25 varchar(255),
    col26 varchar(255),
    col27 varchar(255),
    col28 varchar(255),
    col29 varchar(255),
    col30 varchar(255),
    col31 varchar(255),
    col32 varchar(255),
    col33 varchar(255),
    col34 varchar(255),
    col35 varchar(255),
    col36 varchar(255),
    col37 varchar(255),
    col38 varchar(255),
    col39 varchar(255),
    col40 varchar(255),
    col41 varchar(255),
    col42 varchar(255),
    col43 varchar(255),
    col44 varchar(255),
    col45 varchar(255),
    col46 varchar(255),
    col47 varchar(255),
    col48 varchar(255),
    col49 varchar(255),
    foreign key (client_id) references test_client(id)
);

SET @@cte_max_recursion_depth  = 1000001;

insert into test_client
WITH RECURSIVE nrows(id) AS (
SELECT 1 as id UNION ALL
SELECT id + 1 from nrows WHERE  id<=1000000
)
SELECT id, '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '',
 '' FROM nrows;

create index test_client_id on test_client (id);
5
  • my first question is why have you identical tables in then first place
    – nbk
    Mar 28 at 14:32
  • @nbk Sory, I should've made this clear at the beginning. I actually don't. This is just a simplified example to reproduce the issue. In the real scenario the columns are different, the tables are not the same. I just realized that if I only have the id column and nothing else then mysql never attempts a hash join in the first place and the issue cannot be reproduced. So I added empty columns just for the sake of the reproductin - in the real dataset each column represents something else and has a different column set as it should.
    – samu
    Mar 28 at 14:49
  • So tl;dr: this is just for the sake of reproducing the issue on a close-to-real dataset, without the need to expose actual data.
    – samu
    Mar 28 at 14:50
  • no, as far as i can remember, this is al described in the dokumentation, has joins are actually quit fast
    – nbk
    Mar 28 at 14:52
  • According to the execution plan - hash join seems to be pretty fast. BUT when the planner sees a hash join it seems to decide to do a full sequential scan on the temporary result table to sort it, rather than use the index that's available. When I disable hash joins then (as you can see in the execution plan) it seems to pick up the index as it should and the whole query is fast. So it's not the hash joins that are the issue per se, but their presence seems to be causing the issue.
    – samu
    Mar 28 at 14:55

2 Answers 2

2

The following reformulation may do what the Optimizer should be doing:

select
        tc.*,
        test_join1.*,
        test_join2.*
    from ( SELECT id FROM test_client
                 ORDER BY id
                 LIMIT 10 ) AS t0
    JOIN test_client AS tc  ON tc.id = t0.id
    left join test_join1 on t0.id = test_join1.client_id
    left join test_join2 on t0.id = test_join2.client_id
order by t0.id;   -- yes, repeated
2
  • Yes, this indeed is making the query faster. I mean the planner/optimizer should figure this out by itself from the original query, but I guess this is explicit enough for it to work.
    – samu
    Mar 29 at 20:05
  • I'm marking this answer as the solution. While it does not solve the underlying problem (as it seem to be indeed a bug in mysql 8 optimizer), it does optimize this particular query to the point where it becomes usable. Thank you very much Rick :)
    – samu
    Mar 29 at 20:09
0
ALTER TABLE test_join1 ADD INDEX(client_id);
ALTER TABLE test_join2 ADD INDEX(client_id);

If those don't help, please provide a plain ``EXPLAIN SELECT ...`

4
  • Hey @rick-james, thanks for your answer. I tried, but the results are still the same. Plans look exactly the same with or without the index. I updated my original question and included a plain EXPLAIN plan next to the EXPLAIN ANALYZE plan.
    – samu
    Mar 29 at 9:20
  • What I did notice though is that the plan changes depending on the amount of columns. In the original query I select * from all columns. If I change it to just return id then the query is instant and EXPLAIN only says using index in the extra column. It almost looks like the planner is wrongly assuming a different join method should be used depending on the amount of columns returned (which shouldn't matter, it should only care about columns used for the join, sort or filter)
    – samu
    Mar 29 at 9:21
  • File a bug: bugs.mysql.com . This seems to be yet another case where MySQL ignores the LIMIT, leading to loading the entirety of table2 and table2 into the "join buffer" (instead of simply doing 10+10 indexed lookups).
    – Rick James
    Mar 29 at 17:37
  • I filed the bug as bugs.mysql.com/bug.php?id=114512 - thanks
    – samu
    Mar 29 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.