I'm trying to search for a string in my DB , but it should be able to match any word and not the whole phrase.
Suppose, a 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%' doesn't work in this way.
Can someone suggest a more robust way to search, possible showing the relevance counter also.
I don't mind using PHP also for the above, but prefer to do the search at DB level.
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.
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%'
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 :
If you are using PHP and cannot install anything else, there is a full-PHP implementation of Lucene : Zend_Search_Lucene
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.
When using the like clause take care that it is %variable% or variable% not %variable.
Secondly. to make an affective search use the explode function to break the words, like if I search "learn php" it should search like this: "learn+php" as in Google. It's explode() function.
