CODEWITHSUNDEEP
mysqldatabasedatabase-indexes
Chris Stryczynski
Chris Stryczynski

Reputation: 34071

How to create a index for a string / varchar prefix?

I'd like to create a index to improve the performance on a query like:

SELECT * FROM example WHERE name LIKE 'Chris%'

How can I achieve the above?

Upvotes: 0

Views: 652

Answers (1)

Ike Walker
Ike Walker

Reputation: 65587

That query would benefit from an index on the name column:

alter table example add index name (name);

You could also just index a prefix of the name column if it is a long column:

alter table example add index name (name(50));

Note that the index will only help with trailing wildcards, not leading wildcards. For example, these columns will not benefit from the index:

SELECT * FROM example WHERE name LIKE '%Chris%'
SELECT * FROM example WHERE name LIKE '%Stryczynski'

Upvotes: 1

Related Questions