At 03:31 PM 1/24/2007, Martijn Tonies wrote:
> > 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
> > 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.
>Database Workbench - development tool for MySQL, and more!
>Database development questions? Check the forum!
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