MySQL search within the last 5 characters in a column?

My user table has a column "name" which contains information like this:

Joe Lee
Angela White

I want to search for either first name or last name efficiently. First name is easy, I can do

SELECT * FROM user WHERE name LIKE "ABC%"

But for last name, if I do

SELECT * FROM user WHERE name LIKE "%ABC"

That would be extremely slow.

So I am thinking about counting the characters of the input, for example, "ABC" has 3 characters, and if I can search only the last three characters in name column, that would be great. So I want something like

SELECT * FROM user WHERE substring(name, end-3, end) LIKE "ABC%"

Is there anything in MySQL that can do this?

Thanks so much!

PS. I cannot do fulltext because our search engine doesn't support that.

Answers


The reason that

  WHERE name LIKE '%ith' 

is a slow way to look for 'John Smith' by last name is the same reason that

  WHERE Right(name, InStr(name, ' ' )) LIKE 'smi%'

or any other expression on the column is slow. It defeats the use of the index for quick lookup and leaves the MySQL server doing a full table scan or full index scan.

If you were using Oracle (that is, if you worked for a formerly wealthy employer) you could use function indexes. As it is you have to add some extra columns or some other helping data to accelerate your search.

Your smartest move is to split your first and last names into separate columns. Several other people have pointed out good reasons for doing that.

If you can't do that you could try creating an extra column which contains the name string reversed, and create an index on that column. That column will have, for example, 'John Smith' stored as 'htimS nhoJ'. Then you can search as follows.

 WHERE nameReversed LIKE CONCAT(REVERSE('ith'),'%')

This search will use the index and be decently fast. I've had good success with it.


You're close. In MySQL you should be able to use InStr(str, substr) and Right(str, index) to do the following:

 SELECT * FROM user WHERE Right(name, InStr(name, " ")) LIKE "ABC%"

InStr(name, " ") returns the index of the Space character (you may have to play with the " " syntax). This index is then used in the Right() function to search for only the last name (basically; problems arise when you have multiple names, multiple spaces etc). LIKE "ABC%" would then search for a last name starting with ABC.

You cannot use a fixed index as names that are more than 3 or less than 3 characters long would not return properly as you suggest.

However, as Zane said, it's a much better practise to use seperate fields.


You can use the REGEXP operator:

SELECT * FROM user WHERE name REGEXP "ABC$"

http://dev.mysql.com/doc/refman/5.1/en/regexp.html


If it is a MyIsam table, you may use Free text search to do the same.


Need Your Help

Spring XWSS Message Signing

java spring web-services spring-ws ws-security

I am currently working on a client interface which connects to a third party web service.

Creating radio circular rotating button css and jquery?

jquery html css3

I create this script : you can see here Live Demo