[SOLVED] when is 1 long query slower than 2 short ones?

[pnj]

My question is about efficiency.  I have a table with dates, and a foreign key.  Given a specific date, I want to pull the id and date for the records before and after that date with the matching foreign key.

 

Approach 1 is to pull all records with that foreign key, and then find the relevant date in php.  There will be between 5 and 80 rows returned.

 

select pkey, v_date from v where foreign_key=72 order by v_date;

 

Approach 2 is to run two queries, one to get the entry before, one to get the entry after:

select pkey, v_date from v where foreign_key=72 and v_date<"2006-04-27" order by v_date limit 1;
select pkey, v_date from v where foreign_key=72 and v_date>"2006-04-27" order by v_date desc limit 1;

 

Am I right in thinking that the second approach is far more efficient, or is the overhead of the call to mysql so high that its better to get dozens of results, but avoid calling the extra query?

 

Thanks

 

-pnj

[artacus]

I would go with the second approach. Processing through 80 results in PHP is going to be slower than running 2 short queries in mysql.

[fenway]

Plus you can run those two queries as one UNION statement.

[pnj]

The Union!  I've always wondered when it would be useful.  Thanks Fenway, that's perfect.

 

-pnj

[fenway]

UNION DISTINCT is actually the most useful version, but it doesn't apply in this case.