How to extract relevant data from MySQL?

Question

Hey Friends,

I'm using a table named "url2" with tje MySQL InnoDB Engine. I'm having so many data with full HTML of a Page, URL of the page, and so on.... When I use the following SQL query I am getting lot of results:

SELECT url FROM url2 WHERE html LIKE '%Yuva%' OR url LIKE '%Yuva%' 

The search term yuva can be changes as user request
It will select lot of data, mostly which I don't need, how can i avoid that?
The out put of the above query is
www.123musiq.com
www.123musiq.com/home.html
www.123musiq.com/yuva.html
www.sensongs.com/
www.sensongs.com/hindi.html
www.sensongs.com/yuva.html

The Output i need is
According to the relevancy it should be sorted Like
www.123musiq.com/yuva.html
www.sensongs.com/yuva.html
www.sensongs.com/hindi.html

As from the comment of my Friend i change table to MyISAM,but i am geting 123musiq.com files first about 25 after that i am geting sensongs.how can i get 2 from 123musiq.com and 2 from sensongs.com,order by relevance

Answer 1

Perhaps you want to use LIMIT?

SELECT * FROM url2 WHERE html LIKE '%Yuva%' OR url LIKE '%Yuva%' LIMIT 2

Answer 2

It seems you're asking for a Full Text Index, which in MySQL are only available on MyISAM tables.

Since you're using InnoDB tables, the easiest solution is to create a new (MyISAM) table with only the text content and an index to join with the original table (this also helps with seek efficiency in some common cases).