I have a table that will contain, let's say, tasks. Some tasks are new or in work-in-progress stage, but other tasks will be in archive stage which means they have been dealt with and the chance of going back to them is low. I was thinking that placing the archived tasks in a separate table with the same schema would be wise so that queries to the 'current' tasks are faster. Is it right?
If I need to bring up current tasks with archived tasks (like in a search result), I will simply union the two tables.
Is this right? Will I gain any benefit? I think it is called horizontal fragmentation. I am using MySQL InnoDB. Do I need to do something extra to the table definitions in order to really gain performance benefits?
Thanks!!
a separate table with the same schema
I'm not sure this is ever a good idea, remember, DRY (don't repeat yourself). If you have to change the schema for one, you will have to change the schema for the other, this can lead to bugs.
Also,
premature optimization is the root of all evil
Are your database queries currently running too slow? I am guessing they are not.