Mysql php keyword search

Question

I'm trying to create a keyword search on three fields title, author and text. I want them sorted by relevance so I give one point for a match in any of the fields. I also have a table called tags that stores in each row a particular tag for a story. If there is a match on tag, I add to the relevance how many times the tag was used. When a user enters a search, he may enter a sequence of keywords. I was thinking of exploding the string and using a for loop to add for each keyword the code in the select statement below. This seems rather complicated so I was wondering if there is a better way to do this?

select text, ((case when S.sid in (select sid from tags where tahname like 'db') 
           then (select uses from tags T where S.sid = T.sid and tagname like 'db') +
              (case when title like '%db%' then 1 else 0 end) +
              (case when author like '%db%' then 1 else 0 end) +
              (case when text like '%db%' then 1 else 0 end)) as relevance

from stories S
order by relevance desc

Thank you.

Bill Karwin · Accepted Answer · 2013-08-04 23:31:27Z

2

Yes, there are better ways to do keyword search.

The method you're using will force a table-scan which means it has to read every row in the table. Depending on how much data you'll eventually have in your table, this can run hundreds or thousands of times slower than using a specialized full-text indexing method.

Check out my presentation Full Text Search Throwdown which contains a comparison of different solutions. Included are:

edited Aug 4, 2013 at 23:31

answered Nov 22, 2012 at 22:30

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

2 Comments

user1832628 Over a year ago

Hi, thank you for your response. I'm new to mysql so I'm not quite sure what the difference is between MyISAM tables and InnoDB tables but I am using InnoDB tables. I assume that when you say (coming in mysql 5.6) that it is not yet available for InnoDB tables?

Bill Karwin Over a year ago

Right, it's a new feature they're developing for the next release of MySQL.

E_p · Accepted Answer · 2012-11-22 22:29:20Z

1

You can use full text search feature of mysql

http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

Or even better use something that was developed for searches

http://sphinxsearch.com/

edited Nov 22, 2012 at 22:29

answered Nov 22, 2012 at 22:26

E_p

3,14419 silver badges30 bronze badges

Collectives™ on Stack Overflow

Mysql php keyword search

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related