I've Never Understood This About Match

Databases
1

I feel that Match for FullText search is resource intensive. But what I’ve never understood is, well, look at this sample:


SELECT news.news_id,
            MATCH(news.title, news.description) AGAINST ('+$keyword' IN BOOLEAN MODE) AS score
        FROM news
        WHERE MATCH(news.title, news.description) AGAINST('+$actorsearchname' IN BOOLEAN MODE) >1.3",$doit) 
		or die(mysql_error());

Why couldn’t this every just be:


SELECT news.news_id,
            MATCH(news.title, news.description) AGAINST ('+$keyword' IN BOOLEAN MODE) AS score
        FROM news
        WHERE score >1.3",$doit) 
		or die(mysql_error());

I feel like writing out the Match twice is using double resources. Am I wrong?

Thanks
Ryan

2

Ok, I realized using HAVING works, but is that more efficient?

Cheers
Ryan

3

no, it’s not

4

Yeah, I ran an explain and it proved that it definitely was not.

Since the query is so resource intensive (I have 300K rows full of info it’s searching), I first ran a query grabbing only the additions from the previous month (instead of the last 3 years) to shrink how many rows to browse. So the new query is:


SELECT news.title, news.link, news.description, news.date, related_sites.site_name,
            MATCH(news.title, news.description) AGAINST ('+keyword' IN BOOLEAN MODE) AS score
        FROM news,related_sites
        WHERE news.news_id IN ($limitedids) AND MATCH(news.title, news.description) AGAINST('+keyword' IN BOOLEAN MODE) >1.3 AND news.site_id=related_sites.site_id ORDER BY news.news_id DESC LIMIT $min,10

With that IN ($limitedids), that ensures that the query only does the fulltext search on those listed row id numbers, right?

Cheers!
Ryan

5

i would also like to think so, yes :slight_smile:

but only an EXPLAIN can tell you for sure

disclaimer: i am no expert on EXPLAINs