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 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;
My 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 between
UNIX_TIMESTAMP('2010-04-01 00:00:00') AND UNIX_TIMESTAMP('2010-04-30
23:59:50') GROUP BY FlightRoutes.Dep, FlightRoutes.Des ORDER BY
COUNT(FlightRoutes.FlightID) LIMIT 20;
Suggestions :-
1. Do some research on Query caching because both the tables used are
MYISAM, Query Cache can be useful on MYISAM tables (Provided query build
values are static).
2. Run "Analyze" and "Optimize" commands on the IVAOData table. It will help
updating the index statictics and defragmenting the table (the table will
respond better to queries).
try these can get back with your observations.
Cheers,
Anirudh Sundar
On Fri, May 7, 2010 at 12:51 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') 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
>
>
