inactive records

[ann_dupont]

Hi,

I use in my php application queries with unions and joins... In each table are a few ten thousands of records and it is getting a bit slower on some requests (the indexes I use are OK).
Because most new data is only used for maximum 3 months, I thought on getting rid of the 'inactive' records, but not deleting them (search purposes).
Does anyone has any experience with this?
I had two ideas:
1. copy the inactive rows to 'archive tables' where I can still search data, and I could transfer a row back to the active table if it should be needed again.
2. I add an extra field to each table: 0 = inactive, 1 = active; in queries I could make a difference with that field included, avoiding the inactive ones.

In both cases, I need to rewrite my php-scripts, but idea n°2 looks the easiest one. Could this help me to keep the querying fast?

Thank you for any suggestion!

Ann

[r937]

an inactive flag probably won't make the queries run faster

even if the indexes are OK (what does that mean?), you need to EXPLAIN the various queries to ensure that each query is using the most appropriate index

separate archive tables will probably work, but any query that needs archive data along with current data will be even slower