Re: More grist for the PostgreSQL vs MySQL mill

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(at)gmail(dot)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/
>