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.

  • What platform is this for? SQL Server? PostgreSQL?
    – Hannah Vernon
    Feb 27, 2015 at 23:16
  • This would be MySQL.
    – user45867
    Feb 27, 2015 at 23:51
  • What do the SELECTs look like? In order to tackle this task by the ideas you suggested, or other ideas, we need to look at all the main SELECTs to see what combination of INDEXes, PARTITIONing, UNIONs, etc would be most effective.
    – Rick James
    Feb 28, 2015 at 5:50
  • If you end up with "sweeping closed tickets into an archive", I would recommend doing it continually and gradually, using techniques discussed in mysql.rjweb.org/doc.php/deletebig .
    – Rick James
    Feb 28, 2015 at 5:51

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.