mysql - How can I create an index on a substring of a text column?

Question

I quite often need to filter records in a specific table on the basis of the existence of a substring in a text column. Specifically I need to exclude records that contain a /.

I currently use a WHERE statement such as:

WHERE table_name.text_col NOT LIKE "%/%"

My hunch is that searching the strings of every record for this substring takes a long time (relatively) and could be improved by indexing in some way. I could create a new binary indexed column and populate this based on whether the text column contains / but I was wondering if there is a neater solution for this?

I found this question which refers to a LEFT() style solution but I didn't understand the syntax and I'm looking for something that can cope with the substring being anywhere in the string.

Answer 1

You could create a computed column that persistently stores the information:

alter table table_name 
    add column text_has_slash tinyint
    generated always as (text_col like '%/%')
    stored
;

Or, if you want to treat null values as negatives:

    generated always as (coalesce(text_col like '%/%', 0))

The column value is computed and stored in the table (it is automatically updated by the database when the value changes).

Now you can use that column in your query:

select * from table_name where not text_has_slash;

Demo on DB Fiddle

Filtering on a pre-computed value should already improve performance.

Creating an index on a boolean column does not necessarily help, because there are only three possible values (0, 1, null). Unless the values are very unevenly distributed, it is often faster for the database to perform a full scan. On the other hand, you might want to include this column in a multi-column index, if you have more search criteria than those you have shown.