1

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.

Flukey
  • 6,445
  • 3
  • 46
  • 71
  • http://stackoverflow.com/questions/4944174/mysql-full-text-or-sphinx-or-lucene-or-anything-else http://stackoverflow.com/questions/4774227/fulltext-search-with-mysql-ft-or-sphinx http://stackoverflow.com/questions/6820527/lucene-or-mysql-full-text-search – Mauricio Scheffer Sep 23 '11 at 12:39
  • possible duplicate of [Pros & cons of full text search engine Lucene, Sphinx, Postgresql full text search, MySQL full text search?](http://stackoverflow.com/questions/737275/pros-cons-of-full-text-search-engine-lucene-sphinx-postgresql-full-text-searc) – Mauricio Scheffer Sep 23 '11 at 12:39
  • @MauricioScheffer - these questions do not answer my question as it's searching for words. I'm doing a wildcard search and wondering whether it exists in the various search servers and whether it's more efficient than a MySQL like wilcard. – Flukey Sep 23 '11 at 12:41
  • please edit your question title, make it more specific. – Mauricio Scheffer Sep 23 '11 at 12:55

4 Answers4

1

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.

Anthony
  • 270
  • 1
  • 4
  • 9
0

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.

Daniel
  • 142
  • 4
0

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

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
0

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

Samuele Mattiuzzo
  • 10,760
  • 5
  • 39
  • 63
  • Hi Samuele, a rather long piece of text to read. do you have a simple example of your dataConfig xml file? thanks! :-) – Flukey Sep 23 '11 at 11:56
  • you should check out the example (http://wiki.apache.org/solr/DataImportHandler#Full_Import_Example) since we started exactly from that :) our dataconfig is a bit complex and it's useless to post it here (it'll make more confusion than anything).. you basically choose your driver (jdbc:mysql for mysql), configure the list of fields you want to retrieve and configure the query with standard mysql syntax :) – Samuele Mattiuzzo Sep 23 '11 at 13:06