Partitioning MySQL for "expired" transactions to improve performance

Question

I am dealing with an existing application that uses the database as a sort of transaction log in several cases, for example orders or payments. These tables are large (20 - 60 million rows) and poorly designed -- string keys, denormalized and so on, and performance is poor.

In the cases I am considering, the records could be considered "expired" based on some business logic, and while the data is needed for historical and statistical transactions, at runtime, operations need only apply to unexpired rows. Unexpired rows account for something like 5% of the whole data set.

It's runtime performance I want to improve.

We're not in a position to redesign the system yet, but it occurs to me that some combination of partitions or perhaps conditional indexes might help. For example some data expires monthly, and we could run a monthly task to mark the records expired in a column of the table. One table has a date column which appears to have been designed for this purpose, although it is not even indexed!

So if our runtime queries are scoped to include a condition like "expired_on is not null" my hope is that we could get near-steady-state performance using an index ... But is this a good case to use either or both of a conditional index (only unexpired rows) or partitions, perhaps creating a new one each month to include the prior month's now-expired records?

We could create some sort of archiving system, or roll up tables for historical stats, but for now I am looking to buy time :-). Any suggestions greatly appreciated.

Answer 1

If a lot of your queries have conditions similar to what you describe, e.g. range condition on the date column:

WHERE dateColumn >= (CURRENT_DATE() - INTERVAL 1 MONTH)

WHERE dateColumn >= '2012-01-01' 
  AND dateColumn < '2013-01-01'

it will be useful to define the primary key of the table as (dateColumn, tableAI), where tableAI is an auto incremented integer column.

For InnoDB tables, the above choice means that this index will be the clustered index of the table and any range (on the date) queries on the table will use it and will only scan the relevant part of the table.

If the HasExpired condition is more complex, e.g. some rows should be marked expired after a month, some others after a year, some after a few days, I would consider splitting the table(s) into 2 parts. One having only expired data and one having all data.

So, when some data expires, you'll simply delete them from the "not_expired" table. The data will already be on the other. When you search for not expired data, you'll be searching only one table (the smallest one).
You also have the benefit of adding different indexes on the tables (for example only the above suggestion for the CI in the "all-data" table and many indexes on the not-expired table, depending on your queries.) Without the split, you would need to have an HasExpired column and attach it to all your indexes (as first column), to have equally good indexes.

If the (not-expired) data is updated many times, it might be beneficial to have the split as "not-expired" and "expired" data. So you won't need to do any updates on the "expired" table. But when some data expires, you'll simply move them (instead of deleting) from the "not_expired" table to the "expired" one.

Answer 2

Given your problem, either indexing or horizontal partitioning should improve your performance significantly. I would think an index would perform better, as it would eliminate the need for a table scan, whereas the partition would only reduce the # of rows scanned. MySQL doesn't have such a thing as a conditional index, but a plain index would work well. Your best performance improvement, imo, would be by the addition of an indexed is_expired bit flag.