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
