On 7/18/13 7:03 PM, Wade Shearer wrote: > Imagine a database with first and last names in separate columns. I > need to be able to search by "<first name> <last name>" or "<last > name> <first name>". Is there a better way than this? > > SELECT lastname, firstname FROM wherever WHERE (CONCAT(lastname, ', > ', firstname) LIKE "%Ray Hunter%" OR CONCAT(firstname, ' ', lastname) > LIKE "%Ray Hunter%" )
If you have a decent number of rows, that will get really slow. William's way will work, but will probably not be significantly faster. My first question would be, do you really need the %'s for your search? It depends on what you're doing with it, but removing those would help with William's method, since you could then index one or both columns. If the database is big enough, I'd look into using a separate search engine such as Sphinx, Solr, Lucene, etc. Steve _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
