Where to index - over 15m records and growing

am 07.05.2010 08:44:00 von Chris Knipe

Re: Where to index - over 15m records and growing

am 07.05.2010 09:17:13 von Anirudh Sundar

--001636ed677d2bac0f0485fbd945
Content-Type: text/plain; charset=ISO-8859-1

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 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
>

--001636ed677d2bac0f0485fbd945--

Re: Where to index - over 15m records and growing

am 07.05.2010 09:21:30 von Chris Knipe

--001636499f9d7753410485fbe896
Content-Type: text/plain; charset=ISO-8859-1

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 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 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

--001636499f9d7753410485fbe896--

Re: Where to index - over 15m records and growing

am 07.05.2010 10:42:33 von Rob Wultsch

Added whitespace for readabilty:
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;

First thing that pops to mind: Do you *really* mean left join?

Second thing:
How selective is
"WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "

Test by running
SELECT COUNT(*)
FROM IVAOData
WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')

If this is a large proportion of the row count then you are probably
in store for pain. It sounds like you are matching half the table. Big
(intermediate) result sets often end in pain.

Third thing:
My (rather sleepy) gut thinks your best bet is a a composite index on
the table IVAOData on the columns TrackerTime and FlightID. This will
make all access to the table in this query hit a covering index.

Fourth thing:
What do you intend to ask the database with this query. This query
smells overly broad.

--
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Where to index - over 15m records and growing

am 07.05.2010 11:08:39 von Chris Knipe

--0016e6db2996ab01200485fd67bd
Content-Type: text/plain; charset=ISO-8859-1

On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch wrote:

> Second thing:
> How selective is
> "WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
> AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "
>
> Test by running
> SELECT COUNT(*)
> FROM IVAOData
> WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
> AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
>
> If this is a large proportion of the row count then you are probably
> in store for pain. It sounds like you are matching half the table. Big
> (intermediate) result sets often end in pain.
>

At this stage, you are correct. We have roughly 2 months worth of data in
the table and are selecting about half (one months worth), thus about 50%.
With 6 months worth of data in the table and selecting one months worth of
data, that's roughly 16% of the data - but it will still be a bulk large
result... Hmmm, something tells me I need to rethink this yes.

> Third thing:
> My (rather sleepy) gut thinks your best bet is a a composite index on
> the table IVAOData on the columns TrackerTime and FlightID. This will
> make all access to the table in this query hit a covering index.
>

Took over 12 hours to create the index on TrackerTime, and you're right - I
should have seen and realised this. I will drop the index on TrackerTime
and re-create it using both colums as I should have done in the first place.

> Fourth thing:
> What do you intend to ask the database with this query. This query
> smells overly broad

The idea is to get a count of the number of entries from Dep to Des during
the last month. I.E. How many records are there where Dep and Des are the
same during the last month. With some changes to the application that
captures the data in the first place, I should be able to drop the need for
this query completely. You have made me think a bit here and you're right.
This is not the way to do it.

I'll rethink this a bit more and come up with something better.

PS - Started the query before my first email was even posted, it's still
running... 3948 Seconds the last time I checked...

--

Regards,
Chris Knipe

--0016e6db2996ab01200485fd67bd--

Re: Where to index - over 15m records and growing

am 07.05.2010 17:05:59 von Peter Brawley

--------------090105040100040506060707
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>something tells me I need to rethink this yes.

If you were to add a computed column yearmonth, you could write WHERE yearmonth=201004.

PB

-----

Chris Knipe wrote:
> On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch wrote:
>
>
>> Second thing:
>> How selective is
>> "WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>> AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "
>>
>> Test by running
>> SELECT COUNT(*)
>> FROM IVAOData
>> WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>> AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
>>
>> If this is a large proportion of the row count then you are probably
>> in store for pain. It sounds like you are matching half the table. Big
>> (intermediate) result sets often end in pain.
>>
>>
>
> At this stage, you are correct. We have roughly 2 months worth of data in
> the table and are selecting about half (one months worth), thus about 50%.
> With 6 months worth of data in the table and selecting one months worth of
> data, that's roughly 16% of the data - but it will still be a bulk large
> result... Hmmm, something tells me I need to rethink this yes.
>
>
>
>
>> Third thing:
>> My (rather sleepy) gut thinks your best bet is a a composite index on
>> the table IVAOData on the columns TrackerTime and FlightID. This will
>> make all access to the table in this query hit a covering index.
>>
>>
>
> Took over 12 hours to create the index on TrackerTime, and you're right - I
> should have seen and realised this. I will drop the index on TrackerTime
> and re-create it using both colums as I should have done in the first place.
>
>
>
>
>> Fourth thing:
>> What do you intend to ask the database with this query. This query
>> smells overly broad
>>
>
>
> The idea is to get a count of the number of entries from Dep to Des during
> the last month. I.E. How many records are there where Dep and Des are the
> same during the last month. With some changes to the application that
> captures the data in the first place, I should be able to drop the need for
> this query completely. You have made me think a bit here and you're right.
> This is not the way to do it.
>
> I'll rethink this a bit more and come up with something better.
>
> PS - Started the query before my first email was even posted, it's still
> running... 3948 Seconds the last time I checked...
>
>
>
>
> ------------------------------------------------------------ ------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.437 / Virus Database: 271.1.1/2859 - Release Date: 05/07/10 06:26:00
>
>

--------------090105040100040506060707--

Re: Where to index - over 15m records and growing

am 07.05.2010 17:13:12 von Johnny Withers

--0016e6d77c4570d9770486027f73
Content-Type: text/plain; charset=ISO-8859-1

You could be running into this:

http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-sc an.html

On Fri, May 7, 2010 at 10:05 AM, Peter Brawley
wrote:

> something tells me I need to rethink this yes.
>>
>
> If you were to add a computed column yearmonth, you could write WHERE
> yearmonth=201004.
>
> PB
>
> -----
>
> Chris Knipe wrote:
>
>> On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch wrote:
>>
>>
>>
>>> Second thing:
>>> How selective is
>>> "WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>>> AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50') "
>>>
>>> Test by running
>>> SELECT COUNT(*)
>>> FROM IVAOData
>>> WHERE IVAOData.TrackerTime >=UNIX_TIMESTAMP('2010-04-01 00:00:00')
>>> AND IVAOData.TrackerTime <=UNIX_TIMESTAMP('2010-04-30 23:59:50')
>>>
>>> If this is a large proportion of the row count then you are probably
>>> in store for pain. It sounds like you are matching half the table. Big
>>> (intermediate) result sets often end in pain.
>>>
>>>
>>>
>>
>> At this stage, you are correct. We have roughly 2 months worth of data in
>> the table and are selecting about half (one months worth), thus about 50%.
>> With 6 months worth of data in the table and selecting one months worth of
>> data, that's roughly 16% of the data - but it will still be a bulk large
>> result... Hmmm, something tells me I need to rethink this yes.
>>
>>
>>
>>
>>
>>> Third thing:
>>> My (rather sleepy) gut thinks your best bet is a a composite index on
>>> the table IVAOData on the columns TrackerTime and FlightID. This will
>>> make all access to the table in this query hit a covering index.
>>>
>>>
>>>
>>
>> Took over 12 hours to create the index on TrackerTime, and you're right -
>> I
>> should have seen and realised this. I will drop the index on TrackerTime
>> and re-create it using both colums as I should have done in the first
>> place.
>>
>>
>>
>>
>>
>>> Fourth thing:
>>> What do you intend to ask the database with this query. This query
>>> smells overly broad
>>>
>>>
>>
>>
>> The idea is to get a count of the number of entries from Dep to Des during
>> the last month. I.E. How many records are there where Dep and Des are
>> the
>> same during the last month. With some changes to the application that
>> captures the data in the first place, I should be able to drop the need
>> for
>> this query completely. You have made me think a bit here and you're
>> right.
>> This is not the way to do it.
>>
>> I'll rethink this a bit more and come up with something better.
>>
>> PS - Started the query before my first email was even posted, it's still
>> running... 3948 Seconds the last time I checked...
>>
>>
>>
>> ------------------------------------------------------------ ------------
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com Version: 8.5.437 / Virus Database:
>> 271.1.1/2859 - Release Date: 05/07/10 06:26:00
>>
>>
>>
>

--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6d77c4570d9770486027f73--

Re: Where to index - over 15m records and growing

am 08.05.2010 06:55:42 von Anirudh Sundar

--e0cb4e887955e6853604860dfcb6
Content-Type: text/plain; charset=ISO-8859-1

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 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:
> 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 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 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
>
>

--e0cb4e887955e6853604860dfcb6--