I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom.
Is it useful to split this table into a frequently used table with the 100.000 entries and a archive table?
I would have to move approx. 10.000 elements to the archive table every day.
The server logic to find an element would be:
- Search in the frequently used table.
- If not found there, search in the archive table.
I've done some testing with random data in a small table and a big one (10 times more data). A
SELECT query for one specific element took 0.6 times more time in the big table than in the small one. I believe that makes a difference in overall performance with 1000+ queries per second.
The create is
CREATE TABLE IF NOT EXISTS `note` ( `note_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `title` varchar(100) NOT NULL, `content` text NOT NULL, `date_added` datetime NOT NULL, `date_modified` datetime NOT NULL, PRIMARY KEY (`note_id`), KEY `FK_note_user` (`user_id`), CONSTRAINT `FK_note_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The test query was just:
SELECT * FROM `note` WHERE `note_id` = $note_id LIMIT 1
Your second thought was what i meant. The critical transactions only happen on the newest 10% of data. So would splitting make sense?