Query using index with order by

Question

I'm currently checking the slow_query_log with log_queries_not_using indexes enabled of a mysql instance. There is one query that shows up very frequently, it is not a slow query, but a query that doesn't use indexes.

The query has the following structure:

select ...
from `Tb1`
left join `Tb2` on `Tb1`.`id` = `Tb2`.`id_tb1`
left join `Tb3` on `Tb2`.`id_common` = `Tb3`.`id_common`
left join `Tb4` on `Tb2`.`id` = `Tb4`.`id_tb2`
left join `Tb5` on `Tb1`.`id_tb5` = `Tb5`.`id`;

The Explain for this query is the following:

+----+-------------+--------+------------+--------+-----------------+------------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table  | partitions | type   | possible_keys   | key              | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+--------+------------+--------+-----------------+------------------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | Tb1    | NULL       | ALL    | NULL            | NULL             | NULL    | NULL                  |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Tb2    | NULL       | ref    | fk_tb2_tb1_idx  | fk_tb2_tb1_idx   | 4       | schema.tb1.id         |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Tb3    | NULL       | ref    | fk_tb2_tb3_idx  | fk_tb2_tb3_idx   | 4       | schema.Tb2.id_common  |    1 |   100.00 | Using index |
|  1 | SIMPLE      | Tb4    | NULL       | ref    | fk_tb4_tb2_idx  | fk_tb4_tb2_idx   | 4       | schema.Tb2.id         |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | Tb5    | NULL       | eq_ref | PRIMARY         | PRIMARY          | 4       | schema.tb1.id_tb5     |    1 |   100.00 | NULL        |
+----+-------------+--------+------------+--------+-----------------+------------------+---------+-----------------------+------+----------+-------------+

As we can see from the output of the explain, the query does not use any index on Tb1 even though it has indexes on the columns on which are joined with other tables.

QUESTION 1:

Why this query does not uses the index on Tb1 table?

Then, i tried using an "order by" using the id of Tb1 like this:

select ...
from `Tb1`
left join `Tb2` on `Tb1`.`id` = `Tb2`.`id_tb1`
left join `Tb3` on `Tb2`.`id_common` = `Tb3`.`id_common`
left join `Tb4` on `Tb2`.`id` = `Tb4`.`id_tb2`
left join `Tb5` on `Tb1`.`id_tb5` = `Tb5`.`id`
order by Tb1.id;

Getting the following result in the explain:

    +----+-------------+--------+------------+--------+-----------------+-------------------+---------+----------------------+------+----------+-------------+
    | id | select_type | table  | partitions | type   | possible_keys   | key               | key_len | ref                  | rows | filtered | Extra       |
    +----+-------------+--------+------------+--------+-----------------+-------------------+---------+----------------------+------+----------+-------------+
    |  1 | SIMPLE      | Tb1    | NULL       | index  | NULL            | PRIMARY           | 4       | NULL                 |    1 |   100.00 | NULL        |
    |  1 | SIMPLE      | Tb2    | NULL       | ref    | fk_tb2_tb1_idx  | fk_tb2_tb1_idx    | 4       | schema.tb1.id        |    1 |   100.00 | NULL        |
    |  1 | SIMPLE      | Tb3    | NULL       | ref    | fk_tb2_tb3_idx  | fk_tb2_tb3_idx    | 4       | schema.Tb2.id_common |    1 |   100.00 | Using index |
    |  1 | SIMPLE      | Tb4    | NULL       | ref    | fk_tb4_tb2_idx  | fk_tb4_tb2_idx    | 4       | schema.Tb2.id        |    1 |   100.00 | NULL        |
    |  1 | SIMPLE      | Tb5    | NULL       | eq_ref | PRIMARY         | PRIMARY           | 4       | schema.tb1.id_tb5    |    1 |   100.00 | NULL        |
    +----+-------------+--------+------------+--------+-----------------+-------------------+---------+----------------------+------+----------+-------------+

As we can see from the output of explain no. 2, it now uses the primay key as index.

QUESTION 2:

Using this order by: a) Will improve the performance of the query? b) The query performance would be the same. c) Will degrade the performance of the query?

Answer 1

ANSWER 1:

Internally, in case of InnoDB, full table scan is the same as using primary key because InnoDB clusters data by primary key. And it doesn't use any other index because there's no WHERE condition on the first table.

ANSWER 2:

In reality, adding order by won't make any difference because the execution plan will essentially remain the same (and results returned will be the same too). That's again due to the fact that InnoDB clusters data by Primary Key i.e. rows are always internally sorted by primary key.

So if it makes you feel better to not see those queries in the slow query log, do add the order by.

Finally, I know you didn't ask this, but - I really would recommend against using log-queries-not-using-indexes as it makes you focus you on the wrong metrics. Here's my recommended way to go about query analysis: Advanced MySQL slow query logging video tutorial.

Good luck!

Answer 2

the logic of LEFT JOIN - take all rows from LEFT table

because You have not any WHERE conditions - MySQL do not found any reason to use index for tb1 - with index or without index it still will read all rows.

so, general answer for Your question - it will not increase speed, and possible will be slower (depending from data size), when sort operations will require use of temporary tables.

You can make a few tests - for example change LEFT JOIN to INNER, it not correct for logic test, but it will show You different plan. Also You can add WHERE condition for tb1 and see - what happens.