Subject Re: separate table or separate database
Author Sean
--- In, "Marcin Bury" <mbury@...> wrote:

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
> in before_insert trigger.
> this field must have an index!!!
> then searching where date_of_inserting between monday_date and
> is also lighting fast.
> Marcin
> 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
15ms and
> execute time 16ms...
> Indexes..., indexes... - we can't live without them ;-)