CODEWITHSUNDEEP
mysql
user2894829
user2894829

Reputation: 815

Avoid to apply functions on the indexed column

Asume table A in MySQL 5.7, with index on the column name: id name 1 Alice 2 Nancy ...

Now finding out names with length greater than 5. The query is:

select * from A where length(name) >= 5;

However, if applying functions on column with index, then the index will not work. So how to optimize the query to hit the index?

Upvotes: 0

Views: 26

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

Actually, MySQL 8+ finally supports functional indices, so you may add the following index here:

ALTER TABLE A ADD INDEX((LENGTH(name)));

On earlier versions of MySQL, you would need to get more creative. On MySQL 5.7, you could have added a new column name_length to the A table. When you insert a name, also insert the length, and then index the length column:

INSERT INTO A (name, name_length)
VALUES
    ('Gordon', 6);

CREATE INDEX idx ON A (name_length);

SELECT * FROM A WHERE name_length >= 5;

Upvotes: 1

Related Questions