CODEWITHSUNDEEP
mysqlsql
John Owen Chile
John Owen Chile

Reputation: 501

Improve simple SQL select performance

I have two tables with some data ( > 300_000 rows) and this simple query is taking ~1 seconds. Any idea to make it faster?

SELECT a.* 
FROM a 
INNER JOIN b on (a.b_id = b.id)
WHERE b.some_int_column = 2
ORDER BY a.id DESC
LIMIT 0,10

Both, a.b_id and b.some_int_column have indexes. Also, a.id and i.id are integer primary keys.

When I try a explain, it says first it is using some_int_column index, with temporary and filesort.

If I do this same query, but ordering by b.id ASC it takes ~0.2 ms instead (I know this is because in such case I'm ordering by first explain row), but I really need to order by a table.

Is there something I am missing?

Upvotes: 1

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

For this query:

SELECT a.* 
FROM a INNER JOIN
     b
     ON a.b_id = b.id
WHERE b.some_int_column = 2
ORDER BY a.id DESC
LIMIT 0, 10;

The optimal indexes are likely to be b(some_int_column, id), and a, b_id, id).

You might find that this version has better performance with these indexes:

SELECT a.* 
FROM a 
WHERE EXISTS (SELECT 1
              FROM b
              WHERE a.b_id = b.id AND b.some_int_column = 2
             )
ORDER BY a.id DESC
LIMIT 0, 10;

For this query, the indexes should be a(id, b_id) and b(id, some_int_column).

Upvotes: 1

M T Head
M T Head

Reputation: 1290

SELECT a.* 
FROM b 
INNER JOIN a on (b.id = a.b_id)
WHERE b.some_int_column = 2
ORDER BY a.id DESC
LIMIT 0,10

Try this. Because your are filtering on a column in table B, not a column in Table A. This may reduce the volume of data read. Depending on the sql optimizer it may match up all records in the join and then filter out those =2. But reversing it, the optimizer may only match up records in table b to a that are = 2 in your where clause.

Upvotes: 1

Related Questions