6

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?

  • When table size is a big factor in performance, the is often a missing index or an inefficiently written query. Let's take a look at the main queries, plus SHOW CREATE TABLE before embarking on the effort of splitting the table. – Rick James Jan 27 '16 at 3:09
  • It depends on how the data is to be used. In some uses, splitting the table just adds overhead and complexity. In others, critical transactions may complete much faster. – Walter Mitty Jan 27 '16 at 13:48
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.