1

BACKGROUND I have a table that contains event tickets for about 4,000 events.

Currently, the table is about 3 million rows. Roughly every five minutes, the inventory for an event changes (which I retrieve externally), and I DELETE the tickets for the event AND INSERT the new inventory.

These DELETE and INSERT statements are not executed in regular intervals -- there may be 5 or 6 of them within 30 seconds.

I have two indexes on the table. Every SELECT statement on the table starts with WHERE event_id='myevent' (I never need to query across events).

QUESTIONS

  1. Should each event have its own table?
  2. Will it be obviously more efficient and faster to DELETE and INSERT out of separate tables so that these statements can run in parallel for different events, as opposed to in "series"?

IRRATIONAL FEAR? The thought of "managing" what will become 10,000+ tables is intimidating.

The time and cost of changing the database structure is minimal, so I ultimately want to do the best thing -- whatever that is.

Your advice is appreciated.

Alexis_user
  • 427
  • 5
  • 14
dwigtschrute
  • 46
  • 3
  • possible duplicate of [One table or many?](http://stackoverflow.com/questions/200394/one-table-or-many) – Ben Jul 20 '14 at 20:00
  • I don't think. He doesn't want to put many "different" data (with different structures) into a single table but separate in many tables a single structure. – Alexis_user Jul 20 '14 at 20:06
  • Thanks for the thought, Ben. I looked through the potential duplicate and do not believe it is similar. My question specifically relates to the expense of very frequent (and sizable) DELETEs AND INSERTs on a a large table, which is not addressed in the potential duplicate. Thanks again. – dwigtschrute Jul 20 '14 at 21:05

1 Answers1

0

Mr. Schrutt ! How is Mose ?

  1. To have several tables with the same structure (fields) is never a good idea.
  2. No it won't. I guess event_id is an index (primary key maybe ?) so it's already faster.

10 000rows in small ;) you shouldn't be afraid. edit : I've misread. See comments

Give some examples of queries and your schema. (edit your post)

We'll be able to help you to improve your db.

Alexis_user
  • 427
  • 5
  • 14
  • Thank you for your reply! Mose is, of course, tending to the Beet Farm. Yes, 'event_id' is indexed as primary key. Is the expense of re-indexing 3,000,000 rows un-necessary when I can just do it in a separate table with only a couple thousand rows? – dwigtschrute Jul 20 '14 at 20:04
  • 1
    Wow, I've misread you. Many tables with the same structure is really never a good idea (and shouldn't never be thought). If it's too slow, you need to optimize your structure. – Alexis_user Jul 20 '14 at 20:13