> Thank you Marcin. Yes I have an Insert_Time column and it has both
> ascending and descending indices.
>
> I think "week_no" may not work well with huge data within one week.
> The index selectivity would be very low.

It depends really. If you query data for about a week, what would
be the difference in returning 1 week of data with an index on the
actual insert time, or returning 1 week of data with the same week
number. Yes, the insert time might have a higher selectivity, but
return the same number of rows.

I guess you need to measure it :-)

All that being said, if your situation is in such a way that you can
ignore older data, why not remove/transfer it to another table.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

> >
> > I have similar situation - but less records - these are invoices.
> >
> > What I did , I added extra field called week_no. this is integer
> field that
> > stores year week number in the following format:
> > yyyyww - I multiply year by 100 and add week number - i.e 200712
> >
> > Then add an index on this field and searching where week_no = 200712 is
> > lighting fast
> >
> > If you don't want any calculations use date field and put
> date_of_inserting
> > in before_insert trigger.
> > this field must have an index!!!
> >
> > then searching where date_of_inserting between monday_date and
> sunday_date
> > is also lighting fast.
> >
> > HTH
> >
> > Marcin
> >
> > PS . In the mean time I did some test - I also have a table that
> contains
> > more then milion records. It has a column sort of date_of_inserting
> >
> > I tested a query 'where date_of_inserting between monday_date and
> > sunday_date' and got the result of 2833 record with prepare time
> 15ms and
> > execute time 16ms...
> >
> > Indexes..., indexes... - we can't live without them ;-)
> >
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>