Hi All,
I have a huge issue with a query - it copies the entire table to a tmp table
when executing the query - and it's a big ass table Any help and/or
pointers please?
The query:
SELECT COUNT(FlightRoutes.FlightID) AS Count, FlightRoutes.Dep AS Dep,
FlightRoutes.Des AS Des FROM
Hey Chris,
Please send the explain plan for this query, the estimated table sizes (in
MB or GB) and the RAM capacity.
These are also the requisites for helping optimizing your query if
required...
Thanks.
Anirudh Sundar
On Fri, May 7, 2010 at 12:14 PM, Chris Knipe sav...@savage.za.org wrote:
My appologies for leaving that bit out...
mysql EXPLAIN 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')
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')
On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch wult...@gmail.com wrote:
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
something tells me I need to rethink this yes.
If you were to add a computed column yearmonth, you could write WHERE
yearmonth=201004.
PB
-
Chris Knipe wrote:
On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch wult...@gmail.com wrote:
Second thing:
How selective is
WHERE
You could be running into this:
http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html
On Fri, May 7, 2010 at 10:05 AM, Peter Brawley
peter.braw...@earthlink.netwrote:
something tells me I need to rethink this yes.
If you were to add a computed column yearmonth, you could
Hello Chris,
Your Query Build
EXPLAIN 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
