Optimizing MySQL Queries: Is it always possible to optimize a query so that it doesn't use "ALL"

Question

According to the MySQL documentation regarding Optimizing Queries With Explain:

* ALL: A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

Does this mean that any query that uses ALL can be optimized so that it is no longer is doing a full table scan?

In other words, by adding the correct indexes to the table, is it possible to always avoid using ALL? Or are there some cases where ALL is unavoidable, no matter what indexes you add?

Answer 1

It's almost (there are cases where doing full scan is actually cheaper) always possible to optimize ONE query to avoid full scan by creating appropriate indexes. However, if you run multiple queries against the same table there are scenarios when either some of them will end up doing full scan or you'll end up with more indexes then you have columns in your table :-)

Answer 2

Yes, there are some queries where you'd be hard-pressed to produce an appropriate index. For example:

SELECT * FROM mytable WHERE colA * arg0 - colB > arg1

I'm not entirely sure why you'd want to make such a query, though :)

That said, too many indexes will use up more cache memory and disk space, and slow down updates and inserts.