A common issue, just not sure on the keywords.
I have a table of assessments (14k+ records).
Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done').
Most records (13k) are 'done'... so doing a:
WHERE state != 'done'
It should ignore most records, however MySQL ignores the index (presumably due to the low cardinality).
USE/FORCE INDEX, which kind of helps, but was wondering if this is the best approach.
One option is to add a
datetime for when it is done, but not really used at the moment... but would have high cardinality (with
NULL for those 1k records).