User search use MySQL LIKE or integrate a search engine (such as solr, sphinx, lucene etc.)?

In my mysql db I have a user table consisting of 37,000 (or thereabouts) users.

When a user search for another user on the site, I perform a simple like wildcard (i.e. LIKE '{name}%}) to return the users found.

Would it be more efficient and quicker to use a search engine such a solr to do my 'LIKE' searches? furthe开发者_如何转开发rmore? I believe in solr I can use wildcard queries (http://www.lucidimagination.com/blog/2009/09/08/auto-suggest-from-popular-queries-using-edgengrams/)

To be honest, it's not that slow at the moment using a LIKE query however as the number of users grows it'll become slower. Any tips or advice is greatly appreciated.

We had a similar situation about a month ago, our database is roughly around 33k~ and due to the fact our engine was InnoDB we could not utilize the MySQL full-text search feature (that and it being quite blunt).

We decided to implement sphinxsearch (http://www.sphinxsearch.com) and we're really impressed with the results (me becoming quite a 'fanboy' of it).

If we do a large index search with many columns (loads of left joins) of all our rows we actually halved the query response time against the MySQL 'LIKE' counterpart.

Although we havn't used it for long - If you're going to build for future scailablity i'd recommend sphinx.

you can speed up if the searchword must have minimum 3 chars to start the search and index your search column with a index size of 3 chars.

It's actually already built-in to MySQL: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

we're using solr for this purpose, since you can search in 1-2 ms even with milions of documents indexed. we're mirroring our mysql instance with Data Import Handler and then we search on Solr.

as neville pointed out, full text searches are built-in in mysql, but solr performances are way better, since it's born as a full text search engine