Re: Optimizing a query to out-perform MS Access (easy, right?)

[S C]

I'm also facing a similar issue of speed although I'm not really trying to get it to work really fast. My tables are exactly the same across MySql And MSAccess, below is the query that I'm trying to execute (Access sql)

SELECT * FROM

(SELECT * FROM FinalStack As P WHERE DateAppeared=#9/9/2005# AND MismatchStatus='DROPPED') AS T,

(SELECT A.* FROM FinalStack As A,
[SELECT Field1, MAX(DateAppeared) As LastDate FROM FinalStack WHERE DateAppeared<#9/9/2005# GROUP BY Field1]. AS B
WHERE A.Field1=B.Field1 AND A.DateAppeared=LastDate AND A.Field8='INPIPE' AND MismatchStatus <> 'DROPPED' )

As K
WHERE T.Field1=K.Field1

An equivalent query in MySQL just takes over 30 minutes(might be longer as I kill it lookup by this time) while in Access it runs for about 2-3 minutes. The table contains almost a million rows and 20 columns in both MySql and MSAccess. I realize that this is sort of a complex query but why would mysql choke on it while MSAccess not!!

(*Field1 -> transaction id, the FinalStack contains all the transactions that happened, the query tries to identify the transactions say today that are dropped, but had an immediate previous status of INPIPE)

Any suggestions would greatly help. Thanks

S.