1

I have a table called users which contains the columns firstname and lastname.

I am struggling on how to structure the WHERE clause to return results on matched first name, last name, first name plus a space and the last name, and last name plus a comma and the first name. For instance, "John", "Doe", "John Doe", and "Doe, John". It should also work for partial patches (i.e. "John D"). I am thinking of something like the following (substitute ? with the search phrase).

SELECT * FROM people WHERE
firstname LIKE ?
OR CONCAT_WS(" ",firstname,lastname) LIKE ?
OR lastname LIKE ?
OR CONCAT_WS(", ",lastname,firstname) LIKE ?

There is a little flaw with this approach as searching on "John Doe" will also return "John Enders", "John Flaggens", and "John Goodmen", so I will need to add some conditional to just return results when both first and last name match when both are given.

There is also a big flaw with this approach as I have functions in my WHERE clause which prevent the use of indexes and result in significantly reduce performance.

Can I effectively do this using just SQL without using functions in my WHERE clause, or should I user server code (ie PHP) to parse the given search phrase for spaces and commas and create a dynamic SQL query based on the results?

user1032531
  • 24,767
  • 68
  • 217
  • 387
  • If you want to do these types of searches, you might consider the FULL TEXT search capabilities in MySQL. – Gordon Linoff Mar 02 '13 at 19:25
  • @GordonLinoff. Thank you for your comment. May I ask why you recommend doing so? – user1032531 Mar 02 '13 at 19:32
  • You are experiencing problems trying to fit the SQL `like` command to the needs of fuller text search. Full text search might give you a better framework for implementing the features that you want. – Gordon Linoff Mar 02 '13 at 19:34

2 Answers2

1

You ask if you can efficiently do this within MySQL, given your schema design without using FULLTEXT search the simple answer is no, you can't do this efficiently.

You could create some wacky query using LIKE / STRCMP and / or string functions. If you were to take this approach it will be much better to write some application logic to create the query inline rather than trying to write one query that can handle everything. Either way it’s not likely be truly efficient

MySQL 5.6 has the ability to perform FULLTEXT searches within INNODB. If you’re using a lower version you can only do this with MyISAM tables – there are many reasons why MyISAM may not be a good idea.

Another approach is to look at a real search solution such as Lucene, Sphinx or Xapian

Steve
  • 3,673
  • 1
  • 19
  • 24
  • Thanks Steve, I am using MySQL 5.5.28 and am using INNODB. I will look into FULLTEXT as well as your other recommended real search solutions. – user1032531 Mar 02 '13 at 19:41
0

Have you tried regular expressions http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Dirk N
  • 717
  • 3
  • 9
  • 23
  • I am not sure, but I think regexp in the WHERE statement has the same deficiencies of using a function in the WHERE statement. – user1032531 Mar 02 '13 at 21:03