mySQL LIKE query

Question

Hello,

I am trying to search my db for a string. But it should be able to match any word and not the whole phrase.

Suppose table data has text like "a b c d e f g". Then if I search for "d c b" it should be able to show the results.

field LIKE '%d c b%' does not work like that

Can someone suggest a more robust way to search, possible showing the relevance counter also.

I do not mind using PHP also for the above, but prefer to do the search at DB level.

Thank you for your time.

Answer 1

For best results, you need to create FULLTEXT index on your data.

CREATE TABLE mytable (id INT NOT NULL, data TEXT NOT NULL, FULLTEXT KEY fx_mytable_data) ENGINE=MyISAM

SELECT  *
FROM    mytable
WHERE   MATCH(data) AGAINST ('+word1 +word2 +word3' IN BOOLEAN MODE)

Note that to index one-letter words (as in your example), you'll need to set ft_min_word_len to 1 in MySQL confguration.

This syntax can work even if you don't have an index (as long as your table is MyISAM), but will be quite slow.

Answer 2

If you table is in MyISAM, you can use the FULLTEXT search integrated in MySQL : 11.8. Full-Text Search Functions

Though there will be some restrictions (for instance, if I remember correctly, you cannot search on word shorter than X characters -- X generally being 3 or 4).

Another solution would be to use some Fulltext engine, like Lucene, Solr, or Sphinx -- those generally do a better job when it comes to fulltext-searching : it is their job (MySQL's job being to store data, not do fulltext-search)

There have been lots of questions about those on SO ; for instance :

• php mysql fulltext search: lucene, sphinx, or ?
• Choosing a stand-alone full-text search server: Sphinx or SOLR?
• Pros & cons of full text search engine Lucene, Sphinx, Postgresql full text search, MySQL full text search
• how much more performant is sphinx than MySQL default fulltext search?
• And many others (use the... search engine... on the top right of the site ;-) )

If you are using PHP and cannot install anything else, there is a full-PHP implementation of Lucene : Zend_Search_Lucene

Answer 3

I think what you want to do is, for any of the letters:

field LIKE '%d%' or field like '%c%' or field like '%b%'

for all of the letters

field LIKE '%d%' and field like '%c%' and field like '%b%'

Answer 4

In the end, MySQL LIKE clauses are not meant to be used as 'powerful' search tools to do word-based matching. It's a simple tool to find partial phrases. It also isn't known for scaling well, so if you are doing this on a high-end throughput website, you probably will want another solution.

So that being said, there ARE some options for you, to get what you are wanting:

• REGEX support, there is support in MySQL for doing REGEX based searches. Using that, and with a complicated enough REGEX, you can find what you are looking for.

• True Full Text Indexing in MySQL. MySQL does have a way to create FULLTEXT indexes. You need to be using MyISAM data engine, and there are restrictions on what exactly you can, or can't do. But it's much more powerful than the basic 'like' functionality that SQL has. I'd recommend reading up on it if you are interested.

• 3rd party indexers. This is actually the route that most people go. They will use Lucene / Solr, or other similar indexing technologies that are specifically designed for doing full text searching of words with various logic, just like how modern web search engines work. They are extremely efficient because they, essentially, keep their own database where they break everything up and store it in a manner that works best for exactly those types of searches.

Hopefully one of those three options will work for you.