List:General Discussion« Previous MessageNext Message »
From:Chris Knipe Date:May 7 2010 6:44am
Subject:Where to index - over 15m records and growing
View as plain text  
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

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