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: Changing date result automatically
Dates are not internally stored as a specific region format, they're stored as seconds since epoch - just a big number. Output formatting is a presentation layer issue, which you can solve either in your SQL [select date_format(datefield, 'us format string') from table] or in your application. Maybe MySQL looks at the system's locale for default formatting, or there's a config setting, but I've never looked at that - doing it explicitly protects you from annoying glitches when you move to another system that might be configured slightly differently. On Thu, May 6, 2010 at 11:00 PM, Weydson Lima weys...@gmail.com wrote: I was referring to the function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format --- Weydson Lima weys...@gmail.com On Thu, May 6, 2010 at 1:58 PM, Martin Gainty mgai...@hotmail.com wrote: http://lists.mysql.com/commits/60834 date_format system variable is currently an unused system variable Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: weys...@gmail.com Date: Thu, 6 May 2010 11:44:37 -0500 Subject: Changing date result automatically To: mysql@lists.mysql.com Hi, Is there any way to format all dates generated by a MySQL query as US format? What I want to do is having a generic way to use the DATE_FORMAT function on all my date fields, instead of having to enter them manually. Thank you --- Weydson Lima weys...@gmail.com http://bluetoothmono.weydson.com -- The New Busy is not the old busy. Search, chat and e-mail from your inbox. Get started. http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3 -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
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: Queries not in Cache
Can somebody help me with this? Thanks! On Thu, May 6, 2010 at 10:39 AM, Darvin Denmian darvin.denm...@gmail.com wrote: Hello, I've activated the query_cache in Mysql with the variable query_cache_limit value to 1 MB. My question is: How to know what queries wasn't cached because they have exceeded the value of query_cache_limit? **Sorry for my Brazilian Englihs :( Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Queries not in Cache
What queries, precisely, I can't tell you, but you can have a good idea about how your cache performs using the stuff in show global variables; and the online manuals about what it all means :) Look at 'show global variables like %qcache%', for a start. On Fri, May 7, 2010 at 2:22 PM, Darvin Denmian darvin.denm...@gmail.comwrote: Can somebody help me with this? Thanks! On Thu, May 6, 2010 at 10:39 AM, Darvin Denmian darvin.denm...@gmail.com wrote: Hello, I've activated the query_cache in Mysql with the variable query_cache_limit value to 1 MB. My question is: How to know what queries wasn't cached because they have exceeded the value of query_cache_limit? **Sorry for my Brazilian Englihs :( Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
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: Re: Changing date result automatically
Thank you for your explanation. I was just curious if there was an easy way to format the output of the dates.. maybe by using a wildcard expression on the field names :) Weydson Lima On May 7, 2010 3:08am, Johan De Meersman vegiv...@tuxera.be wrote: Dates are not internally stored as a specific region format, they're stored as seconds since epoch - just a big number. Output formatting is a presentation layer issue, which you can solve either in your SQL [select date_format(datefield, 'us format string') from table] or in your application. Maybe MySQL looks at the system's locale for default formatting, or there's a config setting, but I've never looked at that - doing it explicitly protects you from annoying glitches when you move to another system that might be configured slightly differently. On Thu, May 6, 2010 at 11:00 PM, Weydson Lima weys...@gmail.com wrote: I was referring to the function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format --- Weydson Lima weys...@gmail.com On Thu, May 6, 2010 at 1:58 PM, Martin Gainty mgai...@hotmail.com wrote: http://lists.mysql.com/commits/60834 date_format system variable is currently an unused system variable Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: weys...@gmail.com Date: Thu, 6 May 2010 11:44:37 -0500 Subject: Changing date result automatically To: mysql@lists.mysql.com Hi, Is there any way to format all dates generated by a MySQL query as US format? What I want to do is having a generic way to use the DATE_FORMAT function on all my date fields, instead of having to enter them manually. Thank you --- Weydson Lima weys...@gmail.com http://bluetoothmono.weydson.com -- The New Busy is not the old busy. Search, chat and e-mail from your inbox. Get started.http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3 -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: New MySQL InStall
You can dowload the community server here: http://dev.mysql.com/downloads/mysql/ --- Weydson Lima weys...@gmail.com On Thu, May 6, 2010 at 12:59 PM, Michael Abbott damy...@hotmail.com wrote: Can anyone give me some info here I want to install MySQL on a Vista Home Edition Laptop Can anyone tell me what I should download from the site? I want to run a java app. What version is stable and does it matter the operating system? Thank-YouMike _ Win $10,000 from Hotmail! Enter Here. http://go.microsoft.com/?linkid=9729708
Re: Changing date result automatically
You build pretty much any format you desire out of those speifiers. ie: select date_format(now(),'%m/%c/%Y'); - md On Thu, May 6, 2010 at 5:00 PM, Weydson Lima weys...@gmail.com wrote: I was referring to the function: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format --- Weydson Lima weys...@gmail.com On Thu, May 6, 2010 at 1:58 PM, Martin Gainty mgai...@hotmail.com wrote: http://lists.mysql.com/commits/60834 date_format system variable is currently an unused system variable Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: weys...@gmail.com Date: Thu, 6 May 2010 11:44:37 -0500 Subject: Changing date result automatically To: mysql@lists.mysql.com Hi, Is there any way to format all dates generated by a MySQL query as US format? What I want to do is having a generic way to use the DATE_FORMAT function on all my date fields, instead of having to enter them manually. Thank you --- Weydson Lima weys...@gmail.com http://bluetoothmono.weydson.com -- The New Busy is not the old busy. Search, chat and e-mail from your inbox. Get started.http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3 -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
log-slow-queries
Can't get slow querys to log. Does this not work in myisam? *snip* [mysqld] log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 *snip* restarted mysqld - no log. Created in file in /var/log/mysql/ *snip* -rwxr--r-- 1 mysql mysql 0 May 7 10:33 mysql-slow.log *snip* still not writing to the file I've read http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/ looks pretty simple - not sure what I'm missing. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: log-slow-queries
At 12:04 PM 5/7/2010, Stephen Sunderlin wrote: Can't get slow querys to log. Does this not work in myisam? Sure it does. Have you tried: slow_query_time = 1 Mike *snip* [mysqld] log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 *snip* restarted mysqld - no log. Created in file in /var/log/mysql/ *snip* -rwxr--r-- 1 mysql mysql 0 May 7 10:33 mysql-slow.log *snip* still not writing to the file I've read http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/ looks pretty simple - not sure what I'm missing. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: log-slow-queries
At 03:58 PM 5/7/2010, you wrote: At 12:04 PM 5/7/2010, Stephen Sunderlin wrote: Can't get slow querys to log. Does this not work in myisam? Sure it does. Have you tried: slow_query_time = 1 Mike Sorry, ignore that previous message. (Serves me right for trying to remember it from the top of my head.) I'm using (Windows): general_log=0 log-output=FILE log_queries_not_using_indexes=1 long_query_time=3 slow_query_log=1 slow_query_log_file=U:/mysql5.5/data/SLOWLOG.TXT I assume you are outputting the slow query log to a text file and not to a table. Mike *snip* [mysqld] log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 *snip* restarted mysqld - no log. Created in file in /var/log/mysql/ *snip* -rwxr--r-- 1 mysql mysql 0 May 7 10:33 mysql-slow.log *snip* still not writing to the file I've read http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/ looks pretty simple - not sure what I'm missing. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- 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
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 |
Re: log-slow-queries
Hello Stephen, Did u try this ?? mysql show global variables like '%log_output%'; +---+---+ | Variable_name | Value | +---+---+ | log_output| FILE | +---+---+ If only the log_output is FILE, then the slow queries will get logged in the log. mysql set global log_output = FILE; [if you find log_output as TABLE or NONE] Let me know if you have any issues... Cheers, Anirudh Sundar On Fri, May 7, 2010 at 10:34 PM, Stephen Sunderlin stephen.sunder...@verizon.net wrote: Can't get slow querys to log. Does this not work in myisam? *snip* [mysqld] log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 *snip* restarted mysqld - no log. Created in file in /var/log/mysql/ *snip* -rwxr--r-- 1 mysql mysql 0 May 7 10:33 mysql-slow.log *snip* still not writing to the file I've read http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html http://www.mydigitallife.info/2007/01/22/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/ looks pretty simple - not sure what I'm missing. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sundar.anir...@gmail.com
