Where to index - over 15m records and growing
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 | |
Re: Where to index - over 15m records and growing
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)
Re: Where to index - over 15m records and growing
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.comwrote:
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
Re: Where to index - over 15m records and growing
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
wult...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Where to index - over 15m records and growing
On Fri, May 7, 2010 at 10:42 AM, Rob Wultsch wult...@gmail.com 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
Re: Where to index - over 15m records and growing
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 wult...@gmail.com 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
Re: Where to index - over 15m records and growing
You could be running into this:
http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html
On Fri, May 7, 2010 at 10:05 AM, Peter Brawley
peter.braw...@earthlink.netwrote:
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 wult...@gmail.com 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
joh...@pixelated.net
Re: Where to index - over 15m records and growing
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.comwrote:
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.orgwrote:
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 |
