CODEWITHSUNDEEP
mysqlsqldatabaseindexingdatabase-indexes
frankish
frankish

Reputation: 6826

How to use index for VARCHAR field in MySQL with LIKE and partial match?

I have a MySQL table with 42 million records in it. "name" field in this table has index on it (NOT UNIQUE and NOT PK)

If I use 

SELECT x FROM table WHERE name='asdef'

it uses index and I get the result quickly.

IF I use 

SELECT x FROM table WHERE name LIKE '%sd%'

it does not use index even if I use FORCE INDEX or USE INDEX.

I am absolutely in need of doing partial matching. How can I do this while keeping my field as VARCHAR?

Upvotes: 1

Views: 1732

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Well, you have a problem. And, SQL may provide some tools, but they may not solve your problem.

First, is your "partial" search really for a word inside a phrase. If so, you can use MySQL full text search to look for words. You may need to pay attention to the stop word list and minimum search length to make it work for your data.

Second, are the names repeated throughout the table? If so, then normalization will help. For instance, if there are 50 thousand names for the 42 million records, searching through the 50 thousand is much more feasible.

Third, are there a handful of finite terms that you are looking for? If so, then you can add flags into the table that are maintained via triggers.

Fourth, how wide are the rows, independent of name? If the rows are wide, you can make the exhaustive search more efficient by storing name in a separate table that fits and stays in memory.

Upvotes: 2

Related Questions