List:General Discussion« Previous MessageNext Message »
From:Rob Wultsch Date:May 7 2010 8:42am
Subject:Re: Where to index - over 15m records and growing
View as plain text  
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
wultsch@stripped
Thread
Where to index - over 15m records and growingChris Knipe7 May
  • Re: Where to index - over 15m records and growingAnirudh Sundar7 May
    • Re: Where to index - over 15m records and growingChris Knipe7 May
      • Re: Where to index - over 15m records and growingRob Wultsch7 May
        • Re: Where to index - over 15m records and growingChris Knipe7 May
          • Re: Where to index - over 15m records and growingPeter Brawley7 May
            • Re: Where to index - over 15m records and growingJohnny Withers7 May
      • Re: Where to index - over 15m records and growingAnirudh Sundar8 May