I need to do the simple query
SELECT id FROM table WHERE blob_field IS NULL.
blob_field is a longblob. If I create an index on
blob_field with prefix length 1, will this be sufficient to use for this query?
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 only takes a minute to sign up.Sign up to join this community
Yes, a prefix length of one will allow you to have a working index for looking up NULLs.
Here's the SQLFiddle: http://sqlfiddle.com/#!2/12bf3/1/0
The EXPLAIN says that it is using the desired index.
Of course you might want to try a larger test to make sure it performs as well in reality as you hope.