List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:May 7 2010 7:21am
Subject:Re: Where to index - over 15m records and growing
View as plain text  
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:
root@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.anirudh@stripped>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 <savage@stripped> 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

Thread
Where to index - over 15m records and growingChris Knipe7 May
  • Re: Where to index - over 15m records and growingAnirudh Sundar7 May
    • Re: Where to index - over 15m records and growingChris Knipe7 May
      • Re: Where to index - over 15m records and growingRob Wultsch7 May
        • Re: Where to index - over 15m records and growingChris Knipe7 May
          • Re: Where to index - over 15m records and growingPeter Brawley7 May
            • Re: Where to index - over 15m records and growingJohnny Withers7 May
      • Re: Where to index - over 15m records and growingAnirudh Sundar8 May