Is it useful to split a table into a frequently used and a archive table?

Question

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:

1. Search in the frequently used table.
2. If not found there, search in the archive table.

---

Background

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.

---

@Rick James

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 

---

@Walter Mitty

Your second thought was what i meant. The critical transactions only happen on the newest 10% of data. So would splitting make sense?

Answer 1

Personally, I would split that table. Definitely consider Rick James' point about ensuring everything is Indexed properly and is being queried efficiently first, but, at the end of the day, you're removing 90% of the data that you have to sift through to get what you want. Archiving that data will make queries faster by reducing row counts and shrinking the Index(es) considerably. There will be more Indexes because you'll have to duplicate them on your Archive table, so disk space will suffer a little, but my expectation is that performance will improve...

UNLESS: The only part of this that jumps to mind that would KILL what I'm suggesting is if the data being moved to the Archive is accessed more than you think. In that case, you're now querying two tables, totaling to MORE than what you had before (including duplicated Indexes/Keys); not only is there not a benefit, you've now taken more time (with more overhead) than your original setup would have taken.

In short: you need to be VERY sure about how often that Archived data is being accessed, and HOW it's being accessed, before you move it.