From: Peter Brawley
Date: May 7 2010 3:05pm
Subject: Re: Where to index - over 15m records and growing
List-Archive: http://lists.mysql.com/mysql/221524
Message-Id: <4BE42C57.8090505@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------090105040100040506060707"

--------------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 <wultsch@stripped> 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--