CODEWITHSUNDEEP
mysqlsearchlucenesolrsphinx
Flukey
Flukey

Reputation: 6555

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? furthermore? 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.

Upvotes: 1

Views: 710

Answers (4)

Anthony
Anthony

Reputation: 270

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.

Upvotes: 1

Samuele Mattiuzzo
Samuele Mattiuzzo

Reputation: 11038

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

Upvotes: 0

Neville Kuyt
Neville Kuyt

Reputation: 29619

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

Upvotes: 0

Daniel
Daniel

Reputation: 142

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.

Upvotes: 0

Related Questions