Right,
You also have to realize that your first query might return zero results, and MySQL (and maybe this is correct SQL behavior) balks at an empty value set "where table_id in ()".
I would expect that giving the DBMS the whole picture of what you want to do, should allow it to make better decisions on how to retrieve the data.
MySQL Inefficiencies like this seem to hit the performance of a highly normalized database design hard.
On 22/01/07, Chris <dmagick@gmail.com> wrote:Shashank Tripathi wrote:
>> select something from othertable;
>> select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...)
>
>
> This is what MySQL's CEO Martin said in an interview on Slashdot. If
> we can manage two queries as above through, say, a PHP application,
> with each executing in 0.004 seconds, then an optimized subquery needs
> to be beat the 0.008 mark to be a viable alternative.
Not really.
If you have too many values, you have problems.. eg the "select
something from table" returns 100+ records (for example, don't have a
concrete number), you'll run into this problem:
http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html
when you try to put them all in the 'in' clause in the 2nd query.
But as you say not usually a problem in most cases but something you
need to be aware of (and you're only aware of it once you've been bitten
by it heh).
--
Postgresql & php tutorials
http://www.designmagick.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/