Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

Question

I'm wondering if this statement can cause real overhead if many concurrent users send the same request to mySQL InnoDB storage engine. Let's say I have a table that has the user_name, and someone want to find whoever that has the word alex in their username. If I use LIKE as follow with InnoDB engine, do you think it can cause so much over head ? Let's say I index the username field.

Select name from User where user_name LIKE '%alex%';

Answer 1

Maybe, but maybe it's not worth worrying about. Depending on how many rows we're talking about, you might not be able to avoid a scan. How many rows in your table?

An index on the username column won't help this query. At all. Think about a phone book: Very easy to find all the people with the last name starting with Sm, right? Not so easy to find all the people with last names that contains ith (or the first name John, for that matter).

An index might help if you constraint your allowed searches to "starts with" instead of "contains." Which might be a better answer in the long run.

Who's searching for usernames that contain alex, anyway? Is this a common use case? If so you might want to look into alternative mechanisms. In SQL Server we have full-text search; see this StackOverflow question for some ideas on how to solve the same problem in MySQL (but again, keep in mind that it might be a "problem" you don't need to solve).

Answer 2

I was wondering the same the other day, what I found on internet was that the best way to make a search with like and text is 'WORD%' with the % at the end (text that end with word will not be found) if you can't use that your query will go row by row and word by word, reading all the text of each result to find the matches.

Other Options that I saw (it doesn't make too much sense to me) it's to separate the query,

select * from actor where first_name like 'alex%' or first_name like '%alex';

I think there is a full text index that will help a lot (let me see if I can find the info and edit my post)

EDIT: here it's the information of full text index,

first, engine HAVE TO BE MyISAM

add the index text,

ALTER TABLE fulltext_sample ADD FULLTEXT(column_name)

A list of the main features of a standard FULLTEXT search follows:

Excludes partial words
Excludes words less than 4 characters in length (3 or less)
Excludes words that appear in more than half the rows (meaning at least 3 rows are required)
Hyphenated words are treated as two words
Rows are returned in order of relevance, descending
Words in the stopword list (common words) are also excluded from the search results. The stopword list is based upon common English words, so if your data is used for a different purpose, you'll probably want to change the list. Unfortunately, doing so at present is not easy. You'll need to edit the file myisam/ft_static.c. recompile MySQL, and rebuild the indexes! To save you hunting through the source, or if you have a binary version of MySQL, here is a list of stopwords. Note that these can and do change with different versions. To be absolutely sure, you'll have to check the specific list for your version.

With this you can improve your search by fulltext using the '%Axel%'