CODEWITHSUNDEEP
mysqlsql
ktm5124
ktm5124

Reputation: 12123

Can indexing a MySQL column improve a LIKE search?

I'm using a table that I downloaded elsewhere, and they put a column called DATA_SOURCE which concatenates all the different data sources of a record like so:

sourceA; sourceB; sourceC; ...

So, if I am looking for records from sourceB, I would have to do a like search on %sourceB%.

This is obviously a time-consuming query. My question is, if I were to index column DATA_SOURCE, would it improve the performance of these wildcard like searches? Or would it not make a difference.

Upvotes: 2

Views: 74

Answers (1)

Niels Keurentjes
Niels Keurentjes

Reputation: 41958

No. Indexes will be used on like searches, but only if the search criterium does not begin with a wildcard.

So LIKE 'Albert %' will be indexable, while LIKE '%Einstein%' will not.

Reason for this is of course that all an index does is construct an internal table of where the results are when sorted by that column, to reduce a search from linear to logarithmic complexity. If the search criterium starts with a wildcard it will still have to loop through all possible values to match them (a so-called table scan), thus eliminating the potential performance gain of an index.

Upvotes: 6

Related Questions