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') AND
IVAOData.TrackerTime <= UNIX_TIMESTAMP('2010-04-30 23:59:50') GROUP BY
FlightRoutes.Dep, FlightRoutes.Des ORDER BY COUNT(FlightRoutes.FlightID)
LIMIT 20;
+----+-------------+--------------+-------+-------------------+------------+---------+-------------------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows |
Extra |
+----+-------------+--------------+-------+-------------------+------------+---------+-------------------------------+--------+---------------------------------+
| 1 | SIMPLE | FlightRoutes | index | PRIMARY | ixAirports |
14 | NULL | 106216 | Using temporary; Using
filesort |
| 1 | SIMPLE | IVAOData | ref | ixFlightID,ixTime | ixFlightID |
36 | tracker.FlightRoutes.FlightID | 73 | Using
where |
+----+-------------+--------------+-------+-------------------+------------+---------+-------------------------------+--------+---------------------------------+
2 rows in set (0.33 sec)
Table / Index Sizes:
r...@netsonic:/var/lib/mysql/tracker# ls -lah IVAOData.* FlightRoutes.*
-rw-rw---- 1 mysql mysql 8.5K 2010-04-30 08:57 FlightRoutes.frm
-rw-rw---- 1 mysql mysql 9.7M 2010-05-07 01:13 FlightRoutes.MYD
-rw-rw---- 1 mysql mysql 6.1M 2010-05-07 01:39 FlightRoutes.MYI
-rw-rw---- 1 mysql mysql 11K 2010-05-06 11:23 IVAOData.frm
-rw-rw---- 1 mysql mysql 3.9G 2010-05-07 09:19 IVAOData.MYD
-rw-rw---- 1 mysql mysql 1.4G 2010-05-07 09:19 IVAOData.MYI
I expect the IVAOData table to roughly tripple in size. Currently it holds
2 months worth of data, the ideal situation would be to keep 6 months worth
of data in the table...
RAM Size on the machine is 8GB...
Regards,
Chris.
On Fri, May 7, 2010 at 9:17 AM, Anirudh Sundar <sundar.anir...@gmail.com>wrote:
>
> 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
>>
>
>
--
Regards,
Chris Knipe
