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

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:

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

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.

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

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

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

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

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

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

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

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

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:

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

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.

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

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

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.