List:General Discussion« Previous MessageNext Message »
From:Miles Thompson Date:January 24 2007 11:42pm
Subject:Re: best way to query this table
View as plain text  
At 03:31 PM 1/24/2007, Martijn Tonies wrote:

>Hello Randy,
>
> > I have a table that has a column that has a timestamp Column.
> >
> > This table is going to get very large ( 1 or 2 million rows) and will
> > be queried alot.
> >
> > Alot of these queries will be against timestamp column.
> >
> > These queries will be like, getting entries for today, this week and this
>month
> >
> > I have indexed the column, but i am wondering if i should create a
> > couple of int columns that would be day of week, week of year and
> > month and query against these instead.
> >
> > would love you opinions
>
>Indices are all about "selectivity", the higher the selectivity, the better.
>
>The maximum selectitivy is "1", that means that for each row, there's
>a unique value.
>
>If you created columns for days/months, the selectivity for each of
>these will be lower than the selectivity for the timestamp column.
>
>So an index on the timestamp column is better. Make sure that your
>query uses the index and you should be doing well.
>
>Martijn Tonies
>Database Workbench - development tool for MySQL, and more!
>Upscene Productions
>http://www.upscene.com
>My thoughts:
>http://blog.upscene.com/martijn/
>Database development questions? Check the forum!
>http://www.databasedevelopmentforum.com

And all I would add to that, is make certain your queries cover date ranges 
so you maximize use of the index.

Sounds obvious, I know. We sometimes forget the obvious.

Cheers - Miles


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.8/649 - Release Date: 1/23/2007


Thread
best way to query this tableRandy Paries24 Jan
  • Re: best way to query this tableMartijn Tonies24 Jan
    • Re: best way to query this tableMiles Thompson25 Jan