Re: More grist for the PostgreSQL vs MySQL mill

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/20/07 16:52, Michael Nolan wrote:
> I have a MySQL table on our public website that is populated from a similar
> table on our internal site, which runs PostgreSQL.
>
> Recently I was trying to enhance one of our website queries and ran across
> an interesting phenomenon:
>
> The following query runs very quickly in both PostgreSQL (8.1.3) and
> MySQL (
> 5.0.21)
>
> select plr_rated_memid from tnmt_plr where plr_eventid in ('200607163681');

Is this query created by an application? I.e, there might be a list
of PLR_EVENTIDs?

If so, I understand why it is like it is. Otherwise, why not make
it a direct equality?

> (tnmt_plr has around 3.5 million rows in it, and plr_eventid is an indexed
> field.)
>
> Both databases return the correct number of rows (74) in less than a
> second.
>
> However, when I then try to use that query as a subquery to select rows
> from
> another table, things change:
>
> select count(*) from memmast where memid in (select plr_rated_memid from
> tnmt_plr where plr_eventid in ('200607163681');
>
> (memid is also an indexed field in memmast, a table which has about 650,000
> rows in it.)
>
> This query takes about a second on PostgreSQL but takes OVER SEVEN MINUTES
> on MySQL!
> --
> Mike Nolan
>

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFFsp7iS9HxQb37XmcRAkx6AJdnxYd9yxYLHRBf1jCu51y+9WDQAJ4qfvGD
Axhu0LZJXH9HgHDDazFWIA==
=hcs5
-----END PGP SIGNATURE-----