How to limit the range of an insert-update to only look at certain rows/ rows with field value: X?

Question

I posted something similar before, but am still unsure of the technical implementation --- I'm so close to being there to the finished product.

I have a table of tickets, and a stream of ticket updates sent to that table in an insert/ update fashion.

The problem is with each passing week, or day, etc ..... the table of tickets gets bigger and bigger, making the daily "stream" insert/update lookup take longer, and longer, and longer.

There is a way around this (I think).

Tickets where status = "closed" will never see an update --- they don't have to be looked up by insert/ update, in other words. They are also the vast majority of tickets.

So my question --- how the hell do I prevent a lookup of these tickets?

At first I'm thinking ... add a key .... the first one is simply update where ticket_id = ticket_id .... I could add another ... status <> "closed". But I'm not sure if checking this, and ticket_id, would actually save time. Would it? Or does it matter in which order the update keys are?

Second idea is to have two tables ... an active table, and archive table. Send all the updates through the active table, and then maybe once a day, copy all "closed" ticket rows from the active to the archive, and delete them in the active table. This definitely seems like a massive pain ... but it's a possibility. All lookups would require a table union as well using this option (or maybe view).

I'm not sure ... what do you guys think? Anyone have better ideas or thoughts? Thanks.

Answer 1

Unfortunately, MySQL does not allow for filtered indexes (otherwise you can just filter on open tickets only). The next best thing is to do something similar to the two table approach you describe: Partitioned tables.