MySQL Table Structure for Approval of Records
Posted on 2006-10-29
I have a table for example...
tinyint user_approved default '0'
tinyint admin_approved default '0'
index on id
index on (user_approved, admin_approved, expiration)
This table is used to store articles with a title, description, and an expiration date. Articles should only show if they are both approved by the user and admin, and they are not expired.
My question will this suffer a performance hit with large amounts of records say 100,000?
Or would it be better to have a seperate table TABLE _items_pending that holds any "non-approved" records. Then when they are approved insert them into the main _items table. This way the main _item table does not need to have all SELECT statements query on the approved, admin_approved, expiration index?
Keep in mind this could be expanded so that articles have comments, comments have ratings, etc. and used in a much larger application. So im wondering for future development which path is correct as far as scaling and speed go?