CODEWITHSUNDEEP
mysqlsql
Vladimir Cvetic
Vladimir Cvetic

Reputation: 842

Slow mysql query, join on huge table, not using indexes

SELECT archive.id, archive.file, archive.create_timestamp, archive.spent 
FROM archive LEFT JOIN submissions ON archive.id = submissions.id
WHERE submissions.id is NULL 
AND archive.file is not NULL 
AND archive.create_timestamp < DATE_SUB(NOW(), INTERVAL 6 month) 
AND spent = 0 
ORDER BY archive.create_timestamp ASC LIMIT 10000

EXPLAIN result:

+----+-------------+--------------------+--------+--------------------------------+------------------+---------+--------------------------------------------+-----------+--------------------------------------+
| id | select_type | table              | type   | possible_keys                  | key              | key_len | ref                                        | rows      | Extra                                |
+----+-------------+--------------------+--------+--------------------------------+------------------+---------+--------------------------------------------+-----------+--------------------------------------+
|  1 | SIMPLE      | archive            | range  | create_timestamp,file_in       | create_timestamp | 4       | NULL                                       | 111288502 | Using where                          |
|  1 | SIMPLE      | submissions        | eq_ref | PRIMARY                        | PRIMARY          | 4       | production.archive.id                      |         1 | Using where; Using index; Not exists |
+----+-------------+--------------------+--------+--------------------------------+------------------+---------+--------------------------------------------+-----------+--------------------------------------+

I've tried hinting use of indexes for archive table with:

USE INDEX (create_timestamp,file_in)

Archive table is huge, ~150mil records.

Any help with speeding up this query would be greatly appreciated.

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You want to use a composite index. For this query:

create index archive_file_spent_createts on archive(file, spent, createtimestamp);

In such an index, you want the where conditions with = to come first, followed by up to one column with an inequality. In this case, I'm not sure if MySQL will use the index for the order by.

This assumes that the where conditions do, indeed, significantly reduce the size of the data.

Upvotes: 1

Related Questions