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
> 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
> in before_insert trigger.
> this field must have an index!!!
> then searching where date_of_inserting between monday_date and
> is also lighting fast.
> PS . In the mean time I did some test - I also have a table that
> 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
> execute time 16ms...
> Indexes..., indexes... - we can't live without them ;-)