I have a select query (inner join) where I want the last rows inserted (matching criteria), so there is a ORDER BY table.id DESC

Problem is that with this order, query time: 1.515625 seconds.
If I don’t add any order, so it uses default ASC, query: 0.09375 seconds. (But I get the oldest ones, which I don’t want)

I must not use ORDER BY table.id DESC due to the query time, so I went to phpmyadmin and I tried this ALTER TABLE table ORDER BY id DESC ; it says successfully executed, but not really, it appears in ASC order anyways.

That’s the first problem, then I read that you cannot insert or delete data if you want to maintain the DESC order?
There must be a way, because “query time: 1.515625 seconds” is not an option (table will get bigger).

PD: Is an InnoDB table.
First problem “solved”: ‘ORDER BY does not make sense for InnoDB tables because InnoDB always orders table rows according to the clustered index.’

OK getting somewhere now: https://www.google.com/search?q=inner%20join%20order%20by%20desc%20slow&rct=j

With now order: “Using index”, with DESC “Using index; Using temporary; Using filesort”

While MySQL suppor the keywords ASC / DESC when you create an index, they are ignored. The only reason they are allowed is in case it get supported in the future. Indexes are atm. always ordered ASC.

For speeding up the query, try creating a composite key consisting of your WHERE and ORDER BY columns.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.