Slow inner join order query

Question

I have a problem with the speed of query. Question is similar to this one, but can't find solution. Explain says that MySQL is using: Using index condition; Using where; Using temporary; Using filesort on companies table.

Mysql slow query: INNER JOIN + ORDER BY causes filesort

Slow query:

SELECT * FROM companies 
    INNER JOIN post_indices
        ON companies.post_index_id = post_indices.id
    WHERE companies.deleted_at is NULL
    ORDER BY post_indices.id
    LIMIT 1;
# 1 row in set (5.62 sec)

But if I remove where statement from query it is really fast:

SELECT * FROM companies
    INNER JOIN post_indices
        ON companies.post_index_id = post_indices.id
    ORDER BY post_indices.id
    LIMIT 1;
# 1 row in set (0.00 sec)

I've tried using different indexes on companies table:

1. index_companies_on_deleted_at
2. index_companeis_on_post_index_id
3. index_companies_on_deleted_at_and_post_index_id
4. index_companies_on_post_index_id_and_deleted_at

index_companies_on_deleted_at index is automatically selected by MySQL. Stats for same query using above indexes:

1. 5.6 sec
2. 3.4 sec
3. 8.5 sec
4. 3.5 sec

Any ideas how to improve my query speed? Again said - without where deleted_at is null condition query is instant..

• Companies table has 1.3 mil of rows.
• PostIndices table has 3k rows.

UPDATE 1:

Order by post_indices.id is used for simplicity since it's indexed already. But it will be used on other columns of join table (post_indices). So sort on companies.post_index_id wont solve this issue

UPDATE 2: for Rick James

Your query takes only 0.04 sec to accomplish. And explain says that index_companies_on_deleted_at_and_post_index_id index is used. So yes, it works better, but this doesn't solve my problem (need to order on post_indices columns, will do this in future, so id post_indices.id used for simplicity of example. In future it will be for example post_indices.city).

My query with WHERE, but without ORDER BY is instant.

UPDATE 3:

EXPLAIN query. Also I noticed that order of indexes matters. index_companies_on_deleted_at index is used if it's higher (created earlier) then index_companies_on_deleted_at_and_post_index_id. Otherwise later index is used. I mean automatically selected by MySQL.

mysql> EXPLAIN SELECT * FROM companies INNER JOIN post_indices ON post_indices.id = companies.post_index_id WHERE companies.deleted_at IS NULL ORDER BY post_indices.id LIMIT 1;
+----+-------------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------------------------------------------------------+--------+----------+---------------------------------------------------------------------+
| id | select_type | table        | partitions | type   | possible_keys                                                                                                  | key                           | key_len | ref                                                  | rows   | filtered | Extra                                                               |
+----+-------------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------------------------------------------------------+--------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | companies    | NULL       | ref    | index_companies_on_post_index_id,index_companies_on_deleted_at,index_companies_on_deleted_at_and_post_index_id | index_companies_on_deleted_at | 6       | const                                                | 638692 |   100.00 | Using index condition; Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | post_indices | NULL       | eq_ref | PRIMARY                                                                                                        | PRIMARY                       | 4       | enbro_purecrm_eu_development.companies.post_index_id |      1 |   100.00 | NULL                                                                |
+----+-------------+--------------+------------+--------+----------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------------------------------------------------------+--------+----------+---------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM companies USE INDEX(index_companies_on_post_index_id) INNER JOIN post_indices ON post_indices.id = companies.post_index_id WHERE companies.deleted_at IS NULL ORDER BY post_indices.id LIMIT 1;
+----+-------------+--------------+------------+--------+----------------------------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------+
| id | select_type | table        | partitions | type   | possible_keys                    | key     | key_len | ref                                                  | rows    | filtered | Extra                                        |
+----+-------------+--------------+------------+--------+----------------------------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------+
|  1 | SIMPLE      | companies    | NULL       | ALL    | index_companies_on_post_index_id | NULL    | NULL    | NULL                                                 | 1277385 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | post_indices | NULL       | eq_ref | PRIMARY                          | PRIMARY | 4       | enbro_purecrm_eu_development.companies.post_index_id |       1 |   100.00 | NULL                                         |
+----+-------------+--------------+------------+--------+----------------------------------+---------+---------+------------------------------------------------------+---------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM companies USE INDEX(index_companies_on_deleted_at_and_post_index_id) INNER JOIN post_indices ON post_indices.id = companies.post_index_id WHERE companies.deleted_at IS NULL ORDER BY post_indices.id LIMIT 1;
+----+-------------+--------------+------------+--------+-------------------------------------------------+-------------------------------------------------+---------+------------------------------------------------------+--------+----------+--------------------------------------------------------+
| id | select_type | table        | partitions | type   | possible_keys                                   | key                                             | key_len | ref                                                  | rows   | filtered | Extra                                                  |
+----+-------------+--------------+------------+--------+-------------------------------------------------+-------------------------------------------------+---------+------------------------------------------------------+--------+----------+--------------------------------------------------------+
|  1 | SIMPLE      | companies    | NULL       | ref    | index_companies_on_deleted_at_and_post_index_id | index_companies_on_deleted_at_and_post_index_id | 6       | const                                                | 638692 |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | post_indices | NULL       | eq_ref | PRIMARY                                         | PRIMARY                                         | 4       | enbro_purecrm_eu_development.companies.post_index_id |      1 |   100.00 | NULL                                                   |
+----+-------------+--------------+------------+--------+-------------------------------------------------+-------------------------------------------------+---------+------------------------------------------------------+--------+----------+--------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

UPDATE 4:

I've removed non related columns:

| companies | CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`post_index_id` int(11) DEFAULT NULL,
`vat` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`note` text COLLATE utf8_unicode_ci,
`state` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'new',
`deleted_at` datetime DEFAULT NULL,
`lead_list_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_companies_on_vat` (`vat`),
KEY `index_companies_on_post_index_id` (`post_index_id`),
KEY `index_companies_on_state` (`state`),
KEY `index_companies_on_deleted_at` (`deleted_at`),
KEY `index_companies_on_deleted_at_and_post_index_id` (`deleted_at`,`post_index_id`),
KEY `index_companies_on_lead_list_id` (`lead_list_id`),
CONSTRAINT `fk_rails_5fc7f5c6b9` FOREIGN KEY (`lead_list_id`) REFERENCES `lead_lists` (`id`),
CONSTRAINT `fk_rails_79719355c6` FOREIGN KEY (`post_index_id`) REFERENCES `post_indices` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2523518 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

| post_indices | CREATE TABLE `post_indices` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`county` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`postal_code` int(11) DEFAULT NULL,
`group_part` int(11) DEFAULT NULL,
`group_number` int(11) DEFAULT NULL,
`group_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3101 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

UPDATE 5:

Another developer tested same query on his local machine with exactly same data set (dump/restore). And he got totally different explain:

mysql> explain SELECT * FROM companies      INNER JOIN post_indices         ON companies.post_index_id = post_indices.id     WHERE companies.deleted_at is NULL     ORDER BY post_indices.id     LIMIT 1;
+----+-------------+--------------+-------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+----------------------------------------------------+------+-----------------------+
| id | select_type | table        | type  | possible_keys                                                                                                  | key                                             | key_len | ref                                                | rows | Extra                 |
+----+-------------+--------------+-------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+----------------------------------------------------+------+-----------------------+
|  1 | SIMPLE      | post_indices | index | PRIMARY                                                                                                        | PRIMARY                                         | 4       | NULL                                               |    1 | NULL                  |
|  1 | SIMPLE      | companies    | ref   | index_companies_on_post_index_id,index_companies_on_deleted_at,index_companies_on_deleted_at_and_post_index_id | index_companies_on_deleted_at_and_post_index_id | 11      | const,enbro_purecrm_eu_development.post_indices.id |  283 | Using index condition |
+----+-------------+--------------+-------+----------------------------------------------------------------------------------------------------------------+-------------------------------------------------+---------+----------------------------------------------------+------+-----------------------+
2 rows in set (0,00 sec)

Same query on his PC is instant. Have no idea why it is happening.. I've also tried to use STRAIGHT_JOIN. When I force post_indices table to be read first by MySQL, it is blazing fast too. But still it is mistery for me, why same query on another machine is fast (mysql -v 5.6.27) and slow on my machine (mysql -v 5.7.10)

So it seems that problem is MySQL using wrong table as first table to read.

Answer 1

Using the following optimizer hints, should force MySQL to use the plan that your colleague observed:

SELECT * FROM post_indices 
    STRAIGHT_JOIN companies FORCE INDEX(index_companies_on_deleted_at_and_post_index_id)
        ON companies.post_index_id = post_indices.id
    WHERE companies.deleted_at is NULL
    ORDER BY post_indices.id
    LIMIT 1;

If you will be sorting on other columns of post_indices, you will need an index on those columns to make this plan work well.

Note that what is the most optimal plan will depend on how frequent deleted_at is NULL. If deleted_at is frequently NULL, the above plan will be fast. If not, with the above plan one will have to run through many rows of post_indices before a match is found. Note also that for queries with OFFSET, the same plan may not be the most effective.

I think the issue here is that MySQL decides the join order without considering the effects of ORDER BY and LIMIT. In other words, it will choose the join order that it thinks is fastest to execute the full join. Since there is a restriction on the companies table (deleted_at is NULL), I am not surprised that it will start with this table.

Answer 2

Does this work better?

SELECT * FROM companies AS c
INNER JOIN post_indices AS pi
    ON c.post_index_id = pi.id
WHERE    c.deleted_at is NULL
ORDER BY c.post_index_id           -- Note
LIMIT 1;

INDEX(deleted_at, post_index_id)  -- note

For that matter, how fast does it run with the WHERE, but without the ORDER BY?