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

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