MySql select question

Question

I have a search engine. I have to select some data from a table when a user types in the search keywords.

I want to find an alternative to 'LIKE':

SELECT id,text FROM example WHERE text LIKE '$search'

Because the text column has usually loads of words in it and the search term always contains a few words, I don't get accurate results. Is there any other way of doing this?

Have you considered FULLTEXT searches? I would give more information on them myself, but I'm not great at MySQL, really! A quick google will tell give you more information on them, though, if you are interested. — pb149
– pb149, Commented Aug 22, 2011 at 10:54

Maxim Krizhanovsky · Accepted Answer · 2011-08-22 10:55:18Z

3

It's called full-text indexing, but currently it's not supported in InnoDB, only in MyISAM. Alternative is to use third-party indexing, like Lucene, Solr (which provides web service access on top on Lucene), Sphinx...

answered Aug 22, 2011 at 10:55

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Berkowski · Accepted Answer · 2011-08-22 10:56:57Z

0

If your table is MyISAM, you can enable full text searching:

ALTER TABLE table ADD FULLTEXT idx_text (`text`);

answered Aug 22, 2011 at 10:56

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Comments

Nicholas Smith · Accepted Answer · 2011-08-22 10:57:53Z

0

You could take a look at the MySQL Fulltext mechanism, it provides natural language searches in a fairly easy to use way.

answered Aug 22, 2011 at 10:57

Nicholas Smith

11.8k7 gold badges40 silver badges58 bronze badges

Comments

Cybercartel · Accepted Answer · 2011-08-22 11:01:58Z

0

Usually a search engine is using a tree data structure for fast lookups and some sort of a graph to weight the search result. Maybe you want to look into a trie data structure and space-filling-curve. The latter is useful if you want to compare 2 documents. For example if you sort and count all the words you can do a heat map.

answered Aug 22, 2011 at 11:01

Cybercartel

12.6k7 gold badges39 silver badges73 bronze badges

Collectives™ on Stack Overflow

MySql select question

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related