CODEWITHSUNDEEP
mysqlindexingisnull
Arturo E
Arturo E

Reputation: 303

Can you create an index to determine if a text column is null in mysql?

I have a very large (> 100 million rows) table and a query that I need to write needs to check if a column of type TEXT is null. I don't care about the actual text, only if the column value is null or not.

I'm trying to write an index to make that query efficient, and I want the query to be satisfied by only looking at the index e.g. such that the explain output of that query shows Using index instead of Using index condition.

To be concrete, the table I'm dealing with has a structure like this:

CREATE TABLE foo (
    id INT NOT NULL AUTO_INCREMENT,
    overridden TEXT,
    rowState VARCHAR(32) NOT NULL,
    active TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
) ENGINE=InnoDB

And the query I want is this:

SELECT 
    IF(overridden IS NULL, "overridden", rowState) AS state, 
    COUNT(*)
FROM foo 
WHERE active=1
GROUP BY state

The overridden column is a description of why the column is overridden but, as I mentioned above, I don't care about its content, I only want to use it as a boolean flag.

I've tried creating an index on (active, rowState, overridden(1)) but the explain output still shows Using index condition.

Is there something better that I could do?

Upvotes: 0

Views: 712

Answers (1)

Rick James
Rick James

Reputation: 142258

I suspect this would be a good application for a "prefix" index. (Most attempts at using such are a fiasco.)

The optimal order, I think, would be:

INDEX(active,         -- handle the entire WHERE first
      overridden(1),  -- IS NULL possibly works well
      rowState)       -- to make index "covering"

Using index condition means that the selection of the row(s) is handled in the Engine, not the "Handler".

EXPLAIN FORMAT=JSON SELECT ... may provide more insight into what is going on. So might the "Optimizer trace".

Or...

If the MySQL version is new enough, create a "generated column" that persists with the computed value of overridden IS NULL.

Upvotes: 2

Related Questions