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
