MySQL Index for searching for NULL values in text fields

Question

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?

Andrew G · Accepted Answer · 2014-03-17 19:34:04Z

4

Yes, a prefix length of one will allow you to have a working index for looking up NULLs.

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.

answered Mar 17, 2014 at 19:34

Andrew G

2311 silver badge2 bronze badges

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
Commented 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.
– Tom Raganowicz
Commented Jun 27, 2019 at 13:00

Add a comment |

Up_One · Accepted Answer · 2014-03-17 19:15:59Z

-1

Why don't you do like this :

SELECT OCTET_LENGTH(blob_field ) FROM table WHERE predicate ;

answered Mar 17, 2014 at 19:15

Up_One

1,55210 silver badges15 bronze badges

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.
– Thomas Johnson
Commented Mar 17, 2014 at 19:18
this query will do !
– Up_One
Commented Mar 17, 2014 at 19:21

Add a comment |

2 Answers 2