Added whitespace for readabilty:
SELECT
COUNT(FlightRoutes.FlightID) AS Count,
FlightRoutes.Dep AS Dep,
FlightRoutes.Des AS Des
FROM FlightRoutes
LEFT JOIN IVAOData ON FlightRoutes.FlightID=IVAOData.FlightID
WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
GROUP BY FlightRoutes.Dep, FlightRoutes.Des
ORDER BY COUNT(FlightRoutes.FlightID)
LIMIT 20;
First thing that pops to mind: Do you *really* mean left join?
Second thing:
How selective is
"WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "
Test by running
SELECT COUNT(*)
FROM IVAOData
WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
If this is a large proportion of the row count then you are probably
in store for pain. It sounds like you are matching half the table. Big
(intermediate) result sets often end in pain.
Third thing:
My (rather sleepy) gut thinks your best bet is a a composite index on
the table IVAOData on the columns TrackerTime and FlightID. This will
make all access to the table in this query hit a covering index.
Fourth thing:
What do you intend to ask the database with this query. This query
smells overly broad.
--
Rob Wultsch
wult...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
