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