Scanning a mysql table from the bottom

Question

When i run a mysql select statement, it takes very long because i have already previously deleted a very large number of rows.

Is there a way for the table to start scanning from the bottom, as opposed to from the top?

Answer 1

Maybe you need an additional index for your table. It also doesn't hurt to issue OPTIMIZE TABLE and ANALYZE TABLE occasionally. Query performance shouldn't be affected by having deleted rows, even large numbers of rows, provided you have suitable indexes.

Answer 2

A query does not scan the table in any particular order; it might do so if it happens to traverse a particular index in order (e.g. a range scan), which MIGHT be because you used an ORDER BY.

Databases just don't work like that. You cannot rely on their behaviour in that way.

If you're doing a full table scan, expect it to take a while, particularly if you've deleted a lot of rows recently. However, it will take even longer if you have lots of rows.

Ensure that the query uses indexes instead. Looks at the explain plan and make sure it uses indexes.