How to optimize MySQL "Order By Limit 1" in queries that join multiple tables?

Question

So I have a query like this:

SELECT tablea.name, tablea.views from tablea inner 
join tableb on (tablea.id = tableb.id and tablea.balance > 0) 
order by tablea.views asc limit 1

However, the problem is that when I run it, it runs quite slow (4+ seconds). Interestingly, when the 'order by' clause is removed, while keeping the limit 1, it runs in 0.005 seconds (approx).

Even more interestingly: when I don't join it to tableb, i.e.:

SELECT tablea.name, tablea.views from tablea 
where tablea.balance > 0 
order by tablea.views asc limit 1

The query runs in 0.005 seconds usually.

Notes:

• The column views in tablea is indexed
• tablea and tableb have a 1 to 1 relationship in terms of id, and roughly have the same amount of rows.

Why is there such a drastic difference in performance between the first query, the first query when 'order by' is removed, and the second query?

Would there anyway to make the sorting much faster when joining two tables?

Answer 1

One possible explanation as to what is going on here is that MySQL is choosing to do the ordering before it does the actual join. As you saw in your original query when removing the ORDER BY clause, the joining by itself is not a performance problem. One way to get around this would be to wrap your original query in a subquery, and then order it:

SELECT *
FROM
(
    SELECT tablea.name,
           tablea.views
    FROM tablea
    INNER JOIN tableb
        ON tablea.id = tableb.id AND
           tablea.balance > 0
) t
ORDER BY t.views ASC
LIMIT 1

If this works, then it probably confirms what I speculated. In this case, the subquery forces MySQL to only order records which result from the actual subquery. In any case, you should get in the habit of running EXPLAIN on such queries. My guess is that the index isn't being used/effective when joining in your original query.

Reference: Slow query when using ORDER BY