CODEWITHSUNDEEP
mysqlsqlindexinginner-join
Soheil
Soheil

Reputation: 627

MySQL-inner join is so slow even with indexing

there are two tables in my databse: tabel1, table2:

table1: {id, name, code, timestamp}
table2: {id, tag1, tag2, tag3}

an index has been created on all columns in both tables. now I want to run the following query:

SELECT a.* FROM table1 a
INNER JOIN table2 AS b
ON a.id=b.id
WHERE b.tag1=true
ORDER BY a.timestamp DESC, a.id DESC
LIMIT 24

but it is slow (almost 5 seconds, both tables have 1M rows). when I remove WHERE b.tag1=true from the query it runs in .1 sec. what is making this query so slow and how to fix it?

indexes:

table1: {id: index, name: index, code: index, timestamp: index, (id, timestamp): index}
table2: {id:primary, tag1: index, tag2: index, tag3: index}

Upvotes: 1

Views: 752

Answers (1)

Rick James
Rick James

Reputation: 142538

The best way to spell out the indexes is via SHOW CREATE TABLE.

Unfortunately, the JOIN prevents efficient use of ORDER BY with LIMIT.

b: INDEX(tag1, id)
a: INDEX(timestamp, id)

This formulation might be better:

SELECT a.*
    FROM  table1 a
    INNER JOIN  table2 AS b  ON a.id=b.id
    WHERE EXISTS( SELECT 1 FROM table2 WHERE id = a.id AND tag1 = true )
    ORDER BY  a.timestamp DESC, a.id DESC
    LIMIT  24

Please provide EXPLAIN SELECT ... for your version and mine.

Is tag1 usually true? That is, will most rows of a be filtered out or kept?

If id is the PRIMARY KEY for both tables, why have 2 separate tables?

Upvotes: 1

Related Questions