0

I have a customer table having column, Name which is varchar(255). In the app we have a search functionality by name. We have about 8 million records in this table. The query is very obvious. 

SELECT customer_table_columns FROM customer_table WHERE customer_name 
LIKE '%user input customer name%';

Now this query runs considerably slow (takes upto minute) and it annoys the users. I was wondering if there is a way to improve the performance of this query.

There is an index on this column but index type is not defined. Options for index type are FULLTEXT, PRIMARY and UNIQUE.

user890263
  • 159
  • 1
  • 3
  • 13
  • Can you post the schema of the table? Using an index for the column should be considerably better in query speed, but other things like normalizing the data to separate tables and using foreign keys could help sort times, etc. – helion3 Feb 04 '14 at 23:06
  • MySQL and 'LIKE %' and 'millions of records' don't work together nicely. You are indeed doing a full table scan and that has no magical solution. Check answer @ http://stackoverflow.com/a/11419105/1306385 – André Catita Feb 04 '14 at 23:09

1 Answers1

2

Your query runs slowly because you have a wildcard at the beginning of the search string (that is, %).

With such queries, MySQL needs to do a full table scan. And there isn't much you can do about it.

One alternative is to use a full text index to store the names. This will allow rapid searches of words in the name and word prefixes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786