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.
>
> 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 ;-)
>