index 'enabled' fields good idea?

Question

Content of a website is stored in a MySQL database. 99% of the content will be enabled, but some (users, posts etc.) will be disabled. Most of the queries end as WHERE (...) AND enabled

Is it a good idea to create an index on the field 'enabled'?

Answer 1

It wouldn't be much use creating an index just on enabled alone. But it might be useful to create a compound index in which enabled is included, in order to achieve ORDER BY optimisation.

For example if something like this was a common query:

SELECT * FROM things WHERE c1='foo' AND enabled=1 ORDER BY c2

An index on c1 would allow the matching rows to be found quickly, but it would still need to be sorted in a separate step using c2.

An index on c1, c2 should allow the ordering to be done from the index, greatly speeding up the query sorting, but the lack of enabled in the index would defeat that, because it'd have to compile the list of matching rows indexed by c1, then scan each row to check it's enabled before sorting the results by c2.

If the index were enabled, c1, c2 or c1, enabled, c2, the whole WHERE condition and the ORDERing could be taken directly from index, giving faster results.

As always, it'll depend on what queries you're doing; you should EXPLAIN your most-common queries to check for sure what it's doing.