if you have a significant number of rows, a different tool like Sphinx, Solr or Lucene will be orders of magnitude faster than MySQL.
If performance is a big concern, then you might want to run 'explain' on your query & make sure whatever you write isn't resulting in a full table scan (if you have more than 50k or 100k rows, full table scans can be real performance killers). I may be wrong on this, but it might be worth trying a MySQL fulltext index on the table -- you could run explain in this scenario (and try running explain with just a plain index) to see if it makes a significant difference. If your database isn't massive that may give you what you need for decent performance. http://www.amazon.com/High-Performance-MySQL-Optimization-Replication/dp/1449314287/ref=sr_1_1?ie=UTF8&qid=1374247572&sr=8-1&keywords=mysql+optimization <---in this book, they provide a use-case for sphinx where it sped up a site by about 1000 times. If I recall correctly, full text type searches were taking as long as 10 seconds, and they got the speed down to milliseconds with sphinx. Two of the book authors are the founders of the percona mysql fork, and if you've used percona, you'll notice it comes with sphinx support (sphinx storage engine) built in. If you want to stay with regular mysql (and avoiding compiling it with sphinxSE), the sphinx API isn't too difficult to use... it basically creates it's own index which they call RestructuredText -- it's an XMLish type structure that sphinx uses as an index, and it's built specifically for optimized / fast text searching. It comes with PHP examples of API usage. I'm not sure on the performance difference vs ShpinxSE. Solr & Lucene are simliar; but I haven't used either one with MySQL (I know for example that Datastax Enterprise is based on Solandra, which is a mash up of Cassandra and Solr -- and I know it has extremely fast text search capabilities -- but I'm not presently aware of any Solr / Lucene mashups for MySQL). On Fri, Jul 19, 2013 at 9:41 AM, Steve Meyers <[email protected]>wrote: > On 7/19/13 7:09 AM, Walt Haas wrote: > > You didn't specify how the names are organized into their columns. If > > 'Ray' could be in either firstname or lastname and the same with > > 'Hunter' then use UNION instead of OR > > > > firstname = 'Ray' AND lastname = 'Hunter' > > UNION > > lastname = 'Ray' AND firstname = 'Hunter' > > > > will be faster than using an OR > > > > If you know which columns they are in just select on that. > > That's a good point, although whether it's faster depends on the > database. Some (such as MariaDB) will optimize the OR to be the same as > your UNION. > > Steve > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net > _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
