Page 1 of 1

combining sql queries using standard and fulltext indexes

Posted: Mon Jan 29, 2007 6:22 pm
by stubarny
$sql_query_1 = "select * from ( SELECT title, body, post_timestamp FROM ads_live WHERE MATCH(title, body) AGAINST ('engineer' IN BOOLEAN MODE) ORDER BY post_timestamp DESC LIMIT 0,10) as a order by a.a.post_timestamp DESC";

$sql_query_2 = "SELECT title, body, post_timestamp FROM ads_live where country = 'us'";
----------------------------------------------


Hi everyone,

I'm trying to combine an sql query that uses a FULLTEXT index and another that uses standard indexes. (pasted above)

In the first query I'm conducting a fulltex index search of job adverts for the word 'engineer' and ordering by date posted. Then in the second query I'm selecting only adverts posted in the country 'US' using a standard index.

Is there a way to combine these queries efficiently, i.e. so that a temporary table doesn't have to be set up?

Thanks,

Stu

Posted: Mon Jan 29, 2007 10:13 pm
by feyd
Your first query shouldn't require a subquery. After distilling the actual query out of it the mixing of the second query shouldn't be too difficult.