Slow, Basic JOINs with InnoDB

MySQL & MariaDB Other MySQL® Questions
1

Hi,

Recently had a problem that made me scratch my head and think… what the hell is up?

I have a innodb table, 1Mil plus rows, news items, with some information on other tables, which require a JOIN.

Basically, it is a SELECT … FROM news INNER JOIN … ORDER BY id DESC LIMIT 10

The specific page was loading extremely slow, wierd, as I was sorting by an auto inc (INT) PK. I watched the query in MySQL Admin and was suprised to see the query creating (multiple) temporary tables!

Turns out this simple query was JOINING first (3 tables - 1Mil+ rows) and THEN sorting by PK.

To get around the problem I did:
SELECT … FROM (SELECT … FROM news ORDER BY id DESC LIMIT 10) AS tmp INNER JOIN …

I’m suprised however, that the MySQL optimizer doesn’t take the fastest route in the scenario, which seems quite a simple query to interpret.

2

You should have posted EXPLAIN plan for the query so it can be seen better.

Basically I can see why this may be the problem - in MySQL JOIN optimizer is kind of separate from part which looks at ORDER BY optimization. So MySQL selects best join as if there would not be order by and than looks how to do order by with it.

STRAIGHT JOIN and putting the table you order by first usually helps.