4

I have a table for author names with two fields, first_name & last_name, both varchar(55), both nullable (for crazy business logic reasons) although in reality last_name is unlikely to ever be null.

My where clause for the search screen contains:

WHERE CONCAT(a.first_name, " " , a.last_name) LIKE "%twain%"

so that "Twain" or "Mark Twain" can be searched on. The table has about 15,000 rows & is expected to gradually grow, but won't ever be more than double that, and not for years.

I understand that there are many other parts of my query that will affect this, but given just that information, how might I best index this?

If it would make a great difference in performance, making last_name not nullable is an option, but not first_name

TIA!

2

Two options I can think of. First, if you are on MyISAM or InnoDB 5.6+, you could store the concatenation in a separate field and use a FULLTEXT index on that field.

The other option is to index the first_name and last_name fields separately. Then change your query to:

WHERE a.first_name LIKE "twain%" OR a.last_name LIKE "twain%"

Removing the wildcard from the beginning will allow the indexes to be used.

  • 1
    +1... although, storing the concatenation is not the best idea because of the potential for update anomalies. The two columns could be indexed together in a single fulltext index and it might be that this would accomplish the same purpose without duplicating data. – Michael - sqlbot Jul 7 '13 at 5:09
  • The rewrite of the query is important for another reason, in addition to removing the wildcards: any function (e.g. CONCAT()) that is applied to a column in a WHERE clause almost always eliminates the possibility of an index on that column being used, because the function has to be applied to the values from every row before the truthiness of the expression is known. If there are not other conditions on other indexes that can eliminate rows, this means a full table scan every time. – Michael - sqlbot Jul 7 '13 at 5:15
  • Unfortunately, the way Andrew suggested to search separately won't work, as the searcher might enter the full name "Mark Twain". So I don't know if I'm getting a first, a last, or both. My thought is to see if splitting & doing 4 possible searches might not be faster, but better check this part is really slowing it that much – jmadsen Jul 7 '13 at 8:04
2

So, I am restricted to InnoDB & can't make use of FULLTEXT, and needed to stay completely flexible on the LIKE wildcards. For my own query, my solution was finally to write that part as a subquery that ran fast enough to be satisfactory.

Not a real answer to this, I realize, but was best I could come up with. Thank you all for your help

1

If your query is going to look for %something%, an index will not help you on MySQL 5.0. 8.5.3. How MySQL Uses Indexes says:

The following SELECT statements do not use indexes: SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; and SELECT * FROM tbl_name WHERE key_col LIKE other_col; In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

Can your application can be modified to search for:

  1. Name starts with, or
  2. Name contains?

If 1. can be done, index can be created on firstname and lastname individually and a sanitized variable can be used in the LIKE statement like so:

WHERE a.first_name LIKE '$variable%' or a.last_name LIKE '$variable%'

If 2. can be done, keep a watch on slow query logs and see if your query is causing significant performance degradation. If you expect several queries to hit first and last names in the fashion you described, consider the use of FULLTEXT index. Similar question has been answered on StackOverflow

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.