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?

2 Answers 2


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.

  • 1
    Any idea why a length of 0 is not allowed? Given that the 'nullness' takes up actual space and is actually useful, it seems perfectly viable.
    – Timo
    Sep 12, 2016 at 12:05
  • At least for MySQL 8.0.15 the query: SELECT COUNT(1) from table where field is null take ages (got ~4M rows) if I am using INDEX(field(1)) whilst when I am using full length the reponse is immediate. Surprisingly in both cases EXPLAIN for both cases is exactly the same, index is used, same number of rows, 100% filtered etc. This is really surprising. Looking for a ways to improve, I don't want to waste that much space to index full value, just to search ON NULL or not. Perhaps I could use generated column STORED which I could index. Jun 27, 2019 at 13:00

Why don't you do like this :

SELECT OCTET_LENGTH(blob_field ) FROM table WHERE predicate ;
  • Why would that be better than SELECT id ? To clarify, I'm asking about the best way to create an index for the WHERE blob_field IS NULL part. Mar 17, 2014 at 19:18
  • this query will do !
    – Up_One
    Mar 17, 2014 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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