Does "SELECT...WHERE 1" stop MySQL using an index?

user_4623317 0
Viewed 45

MySQL/MariaDB has log_queries_not_using_indexes enabled and this query shows in the log:

SELECT * FROM `client` WHERE 1 ORDER BY `code` ASC;

The client table has a BTREE index defined with the code column. I'm curious why it wasn't used. I understand indexes can still be used with simple ordering.

Is the index not used because of the WHERE 1 clause in the query? Or is using an index simply not necessary with a simple query like that?

This is academic for me, the query runs fast already.

indexing mysql
2 Answers

No, it has nothing to do with WHERE 1. It is to do with the fact that retrieving all rows row-by-row after traversing the btree index on code is likely to be slower than reading the whole lot into memory and doing a sort.

user_18426770

log_queries_not_using_indexes is, in my opinion, useless. If a query is slow enough to matter, it will show up in the slowlog without this setting. And the setting just clutters the slowlog.

WHERE 1 is equivalent to not having a WHERE clause.

The Optimizer will consider using INDEX(code), if you have such, so that it can avoid a sort pass. But...

Plan A: Use such an index, but have to bounce back and forth between the index's BTree and the data's BTree.

Plan B: Ignore the index, but have to do a sort. (The "sort" may or may not be done in memory. For example, a TEXT column will prevent sorting in RAM.)

The Optimizer makes a crude estimate about which would be more efficient. That is, it may deliberately decide not to use an index, thereby leading to the "noise" in the slowlog. I repeat, turn off that flag.

user_17668310
Related