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:
> 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 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;
>
> FlightRoutes:
> mysql> DESCRIBE FlightRoutes;
> +----------+------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------+------+-----+---------+-------+
> | FlightID | char(36) | NO | PRI | NULL | |
> | Dep | varchar(5) | NO | MUL | NULL | |
> | Des | varchar(5) | NO | | NULL | |
> | Route | text | NO | | NULL | |
> +----------+------------+------+-----+---------+-------+
> 4 rows in set (0.00 sec)
>
> mysql> SHOW INDEX IN FlightRoutes;
>
> +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
> +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> | FlightRoutes | 0 | PRIMARY | 1 | FlightID |
> A | 106216 | NULL | NULL | | BTREE | |
> | FlightRoutes | 1 | ixAirports | 1 | Dep |
> A | 3124 | NULL | NULL | | BTREE | |
> | FlightRoutes | 1 | ixAirports | 2 | Des |
> A | 26554 | NULL | NULL | | BTREE | |
>
> +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
> 3 rows in set (0.00 sec)
>
> IVAOData:
> mysql> DESCRIBE IVAOData;
>
> +-----------------------+----------------------------------------------------------+------+-----+---------+-------+
> | Field |
> Type | Null | Key |
> Default | Extra |
>
> +-----------------------+----------------------------------------------------------+------+-----+---------+-------+
> | EntryID |
> char(36) | NO | PRI |
> NULL | |
> | FlightID |
> char(36) | NO | MUL |
> NULL | |
> | isProcessed |
> enum('0','1') | NO | MUL |
> NULL | |
> | TrackerTime | int(10)
> unsigned | NO | MUL | NULL
> | |
> | CallSign |
> varchar(10) | NO | |
> NULL | |
> | VID | mediumint(6)
> unsigned | NO | MUL | NULL |
> |
> | RealName |
> tinytext | NO | |
> NULL | |
> | ClientType |
> enum('ACT','PILOT','FOLME') | NO | |
> NULL | |
> | Latitude |
> float(8,5) | NO | |
> NULL | |
> | Longitude |
> float(9,5) | NO | |
> NULL | |
> | Altitude | smallint(5)
> unsigned | NO | | NULL |
> |
> | GroundSpeed | smallint(5)
> unsigned | NO | | NULL |
> |
> | PlannedAircraft |
> varchar(30) | NO | |
> NULL | |
> | PlannedTASCruise |
> varchar(10) | NO | |
> NULL | |
> | PlannedDepAirport |
> varchar(5) | NO | |
> NULL | |
> | PlannedAltitude |
> varchar(5) | NO | |
> NULL | |
> | PlannedDestAirport |
> varchar(5) | NO | |
> NULL | |
> | Server |
> char(3) | NO | |
> NULL | |
> | Rating |
> enum('1','2','3','4','5','6','7','8','9','10','11','12') | NO | |
> NULL | |
> | Transponder | smallint(4) unsigned
> zerofill | NO | | NULL | |
> | PlannedFlightType |
> enum('','I','V','Y','Z') | NO | |
> NULL | |
> | PlannedDepTime |
> time | NO | |
> NULL | |
> | PlannedActDepTime |
> time | NO | |
> NULL | |
> | PlannedEnroute |
> time | NO | |
> NULL | |
> | PlannedFuel |
> time | NO | |
> NULL | |
> | PlannedAltAirport |
> varchar(5) | NO | |
> NULL | |
> | PlannedRemarks |
> tinytext | NO | |
> NULL | |
> | PlannedRoute |
> text | NO | |
> NULL | |
> | TimeConnected |
> char(14) | NO | |
> NULL | |
> | ClientSoftwareName |
> varchar(10) | NO | |
> NULL | |
> | ClientSoftwareVersion |
> varchar(10) | NO | |
> NULL | |
> | PlannedAltAirport2 |
> varchar(5) | NO | |
> NULL | |
> | PlannedTypeOfFlight |
> enum('','G','M','N','S','X') | NO | |
> NULL | |
> | PlannedPOB | smallint(3)
> unsigned | NO | | NULL |
> |
> | TrueHeading | smallint(3) unsigned
> zerofill | NO | | NULL | |
> | OnGround |
> enum('0','1') | NO | |
> NULL | |
>
> +-----------------------+----------------------------------------------------------+------+-----+---------+-------+
> 36 rows in set (0.00 sec)
>
> mysql> SHOW INDEX IN IVAOData;
>
> +----------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
> +----------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
> | IVAOData | 0 | PRIMARY | 1 | EntryID |
> A | 13130556 | NULL | NULL | | BTREE | |
> | IVAOData | 1 | ixFlightID | 1 | FlightID |
> A | 179870 | NULL | NULL | | BTREE | |
> | IVAOData | 1 | ixProcessed | 1 | isProcessed |
> A | 2 | NULL | NULL | | BTREE | |
> | IVAOData | 1 | ixProcessed | 2 | TrackerTime |
> A | 1193686 | NULL | NULL | | BTREE | |
> | IVAOData | 1 | ixTracker | 1 | VID |
> A | 15744 | NULL | NULL | | BTREE | |
> | IVAOData | 1 | ixTracker | 2 | PlannedDepAirport |
> A | 136776 | NULL | NULL | | BTREE | |
> | IVAOData | 1 | ixTracker | 3 | PlannedDestAirport |
> A | 177439 | NULL | NULL | | BTREE | |
> | IVAOData | 1 | ixTime | 1 | TrackerTime |
> A | 875370 | NULL | NULL | | BTREE | |
>
> +----------+------------+-------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
> 8 rows in set (0.00 sec)
>
> mysql> SELECT COUNT(FlightID) FROM FlightRoutes; ## Grows by a few
> houndred
> records per day.
> +-----------------+
> | COUNT(FlightID) |
> +-----------------+
> | 106216 |
> +-----------------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT COUNT(EntryID) FROM IVAOData; ## Grows by a few thousand
> records per day.
> +----------------+
> | COUNT(EntryID) |
> +----------------+
> | 13130747 |
> +----------------+
> 1 row in set (0.00 sec)
>
>
>
>
> --
>
> Regards,
> Chris Knipe
>
