combining sql queries using standard and fulltext indexes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
stubarny
Forum Newbie
Posts: 10
Joined: Thu Jul 27, 2006 12:53 pm

combining sql queries using standard and fulltext indexes

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply