Is it more efficient to index columns in SQL or create a new table?

user_13668802 0
Viewed 64

I have a SQL table called "EVENT" and a copy table called "PAST_EVENT". The EVENT table has a foreign key to it's corresponding PAST_EVENT. Given that:

  1. Any update made to the EVENT table is also made to the PAST_EVENT table. They are "duplicates".
  2. The EVENT table is written and read to extremely frequently.
  3. The PAST_EVENT table is read to extremely frequently.
  4. When an event in the EVENT table ends, it is deleted from the EVENT table. Thus every event created will eventually only exist in the PAST_EVENT table.

I decided to have a duplicate of information in the EVENT table in the PAST_EVENT table because my application is looking for data about current and ongoing events (only needing to read from the EVENT table), OR it is looking for events that have ended (only needing to read from the PAST_EVENT table). But never both. My rationale is that making SQL queries on a subset of events is quicker than the alternative.

Alternative:

What if instead, I consolidate both tables into one table called EVENT. I would then add a database indexed boolean field, "hasEnded", in order to query for ongoing or ended events.

Which of the aforementioned strategies is more performant?

More Info (update):

  1. Many EVENTs are created each day. Simultaneously, many EVENT rows are being pruned daily because they have ended. Events get deleted from the EVENT table by a chron job that runs every 12 hours and prunes events that have ended.
  2. One EVENT row does not spawn many PAST_EVENTs. Just one (which will be maintained an exact replica of the present state of its corresponding EVENT row).
  3. The primary keys are auto-inc. In addition, when an EVENT is created, a PAST_EVENT is created with the same primary key id for my personal satisfaction.
database-design mysql sql database
1 Answers

"Ludicrous" is the word that comes to mind. In the interest of some definition of "efficiency", you want to duplicate the data and every modification to the data. That just does not seem efficient to me at all.

I would start with soft deletes -- simple a flag as to whether the event is deleted or not. That does a good job of defining the events.

Because the two modes that you need are either everything or just the non-deleted ones, you can then think about optimizing the storage if necessary. One option -- if your database supports them -- is a clustered index on the delete flag. Such clustering is usually not recommended on a binary flag. But if the non-deleted data is small, it can be a win for queries looking for that.

Another alternative is to use partitions. Some databases don't let you change the partition key -- which poses a challenge.

Finally, you could also have a delete trigger on the events table that would load the deleted events into another table. Queries on all events would require unioning them together.

user_11440350
Related