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%';

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).

  • we are talking about 5000 to 10,000 rows of username. MyISAM allows full text search but I am using InnoDB for Transactions, so that's the tradoff. – Alex May 18 '12 at 19:19
  • 2
    If scanning 10,000 rows causes a performance problem, then either you're not on the right platform, your schema is in serious trouble, or your hardware needs a serious kick in the pants. – Aaron Bertrand May 18 '12 at 19:20
  • I think maybe I need to allow start with or end with only, this can minimize the user flexibility but saves some overhead on the server. Any suggestion ? – Alex May 18 '12 at 19:20
  • Starts with will use an index. Like contains, ends with seems gratuitous for usernames. What user is seriously looking for all other users whose username ends with something? Why? – Aaron Bertrand May 18 '12 at 19:21
  • 1
    You should benchmark it on your hardware with the number of rows you expect. Unless we can borrow your server for the night, there is no way we can tell how painful this search will be, especially since we don't know your tolerance for pain (e.g. how long before a query has been running for "too long"?). – Aaron Bertrand May 18 '12 at 19:22

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%'

  • 1
    Two quick comments (1) LIKE 'WORD%' will only be faster than a scan if there is an index on that column (and other factors in the query still allow a seek or range); (2) since LIKE '%alex' will require a full scan, the OR alternative doesn't make sense, since SQL Server doesn't guarantee it will short-circuit when it does find a matching row on the left - it will still have to scan all rows. – Aaron Bertrand May 18 '12 at 19:24
  • @AaronBertrand yeap that's what I mean, about the (2) thanks, I'm agree with you, but I'm going to put a question about what I think hope you can answer it. – jcho360 May 18 '12 at 19:36
  • 1
    Quick note: InnoDB in MySQL 5.6 is supposed to have FULLTEXT texting when it goes out as a GA release. +1 for suggesting the use of FULLTEXT anyway. – RolandoMySQLDBA May 18 '12 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.