List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:January 24 2007 7:31pm
Subject:Re: best way to query this table
View as plain text  
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

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