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 '15 at 23:16
  • This would be MySQL. – user45867 Feb 27 '15 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 '15 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 '15 at 5:51

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, privacy policy and cookie policy

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