More grist for the PostgreSQL vs MySQL mill - Mailing list pgsql-general

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');

(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