using different index types

[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 (pasted above) that uses a FULLTEXT index and another that uses standard indexes.

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.

Please could you advise how the two queries can be combined efficiently so that both requirements are met?

Thanks for your help! :-)

Stu

[fenway]

I'm assuming you're not using the latest version of MySQL, which apparently can use multiple indexes per table.  The "best" way in version 4 is to use the FULLTEXT query as a subquery, and return back the matching UIDs, and then use the PK index for the outer query.