Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

I could 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).

share|improve this question
    
Please mention your MySQL version and the results you get when you try this: instead of what I assume you are doing, WHERE state != 'done' try the opposite and comment on what you see: WHERE STATE IN ('draft','proofing','amends') listing every other possible state except 'done'. It's a little counter-intuitive but at least in 5.6 the optimizer seems to choose the index with WHERE ... IN () but not with WHERE ... != on an ENUM. Also, explain what you mean by use/force index "kind of helps". –  Michael - sqlbot Sep 26 '13 at 11:56
    
Thanks @Michael-sqlbot, I've updated and your suggestion to use WHERE ... IN () seems to help (will continue to test). –  Craig Francis Sep 26 '13 at 13:46
    
Can you add the SHOW CREATE TABLE name; output? What is the engine of the table? –  ypercube Sep 26 '13 at 13:59
    
It's MyISAM at the moment (for a FULLTEXT index)... and the table structure is quite big (storing lots of text/tinytext values)... so using the INDEX is quite important... but most important is working out how to solve this problem in general, as I've had it come up a couple of times before. –  Craig Francis Sep 26 '13 at 14:50
add comment

1 Answer 1

up vote 1 down vote accepted

While similar queries with = or < or <= will consider (and depending on cardinality estimates will use) an index on (state), a query with a condition of this type will not use an index.:

WHERE state <> 'done'

Things you can do instead:

  • write the condition as:

    WHERE (state < 'done' OR state > 'done')
    
  • use UNION:

    SELECT a.*
    FROM assessments AS a
    WHERE state < 'done'
    
    UNION ALL
    
    SELECT a.*
    FROM assessments AS a
    WHERE state > 'done' ;
    
  • write the condition with IN / OR:

    WHERE state IN  ('draft', 'proofing', 'amends', ...)    -- everything except 'done'
    
    WHERE (state = 'draft' OR state = 'proofing' OR state = 'amends' OR ...) 
    
share|improve this answer
    
I was doing state != "done", which seems to be the same as <>... doing the query with an OR or UNION does not seem to have an effect... but the WHERE state IN () does defiantly help... now as I have a fairly long list of states that I don't want to hard code (more could be added), would adding a SHOW COLUMNS really be a good idea (that would defiantly add time). –  Craig Francis Sep 26 '13 at 13:54
    
Add SHOW COLUMNS where? I don't understand what you mean. –  ypercube Sep 26 '13 at 13:58
    
As in use SHOW COLUMNS to return a list of the possible enum values, then use that to build the query (dropping the "done" state)... i.e. so the multiple queries that use this don't need to hard code the possible values (which can change, and will always need to just ignore the "done" state)... I can't imagine that would be a good idea though. –  Craig Francis Sep 26 '13 at 14:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.