Handling long texts/blobs in InnoDB - 1 to 1 relationship, covering index

  I have seen that 1 to 1 relationship is sometimes used for MySQL(InnoDB) to avoid significant performance slowdown. To understand the performance difference, it is necessary to understand how InnoDB stores column values to data blocks. Suppose you have the following "diary" table (for storing blog entries).

CREATE TABLE diary (
  diary_id INT UNSIGNED  AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  post_date TIMESTAMP NOT NULL,
  status TINYINT UNSIGNED NOT NULL,
  rating FLOAT NOT NULL,
  title VARCHAR(100) NOT NULL,
  body TEXT,
  PRIMARY KEY (diary_id), 
  INDEX user_date(user_id, post_date),
  INDEX user_rating(user_id, rating)
) CHARSET utf8 ENGINE=InnoDB;

  "body" column contains diary text, which is pretty large (1KB/row). On the other hand, the rest columns are small enough (less than 50 bytes per row in total). This table is mostly used from applications for fetching titles, not fetching body. 90% SELECT queries will be like this:
1) SELECT user_id, post_date, title FROM diary WHERE diary_id=?

  And the rest 10% SELECT queries will be:
2) SELECT body FROM diary WHERE diary_id=?

  You probably guess that 1) is much faster than 2) because 1) fetches only <50 bytes while 2) fetches +1KB data. But unfortunately, in most cases 1) is as slow as 2). Even though 100% SELECT queries are 1), overall throughput will be poor if diary table is much larger than innodb buffer pool size. The reason is how InnoDB stores column values. The below is a rough image.



  InnoDB stores large texts/blobs separetely from other columns if no more space is available within the same block. The separated page is called "Overflow Page". But this doesn't work for the diary table. This is mainly because diary body(1KB) is much smaller than InnoDB block size(16KB). When inserting diary entries, InnoDB block space is normally available enough to store all columns including body. As a result, body is stored next to the rest columns, and InnoDB blocks are occupied mostly by body.



  InnoDB's I/O unit is per block basis. Even though you do not fetch body, InnoDB internally has to read blocks that contain body. In other words, single InnoDB block can not have many diary entries. So a lot of random disk reads happen here.
  I talked about this topic at the MySQL Conference & Expo this year. I explained two solutions, one is using 1 to 1 relationship and the other is using covering index.

  1 to 1 relationship solution is creating below two tables:

CREATE TABLE diary_head (
  diary_id INT UNSIGNED  AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  post_date TIMESTAMP NOT NULL,
  status TINYINT UNSIGNED NOT NULL,
  rating FLOAT NOT NULL,
  title VARCHAR(100) NOT NULL,
  PRIMARY KEY (diary_id), 
  INDEX user_date(user_id, post_date),
  INDEX user_rating(user_id, rating)
) CHARSET utf8 ENGINE=InnoDB;

CREATE TABLE diary_body (
  diary_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  body TEXT
) CHARSET utf8 ENGINE=InnoDB;

  diary_head table does not have the largest text column(body). So diary_head table size can be much smaller than the original diary table. InnoDB blocks that have diary_head's column values are frequently accessed, but the number of the blocks can be smaller. So these InnoDB blocks will be cached within InnoDB buffer pool very well. Most of SELECT queries do not require random disk i/o, so total throughput can be increased.

  Another approach is creating a covering index that covers all columns except body. You do not need to create 1 to 1 tables in this case.

mysql> ALTER TABLE diary ADD INDEX diary_covering (diary_id, user_id, post_date, 
    -> status, rating, title); 

  By fetching these columns through diary_covering index instead of the PRIMARY key, InnoDB does not read blocks that contain body. diary_covering index size will be as small as the above diary_head table, so it will be cached very well.

  Both 1 to 1 relationship and an additional covering index perform pretty well(The benchmarking results are written in the conference slides). But neither is straightforward for developers. Using 1 to 1 relationship forces developers to use joins to fetch all diary columns, and forces them to maintain consistency (or use foreign key constraints). Using additional covering index sometimes increases data size significantly. And more, you need to add FORCE INDEX in the SELECT statement like below so that MySQL can use the covering index insted of the PRIMARY key.

mysql> SELECT user_id, post_date, title FROM diary 
    -> FORCE INDEX (diary_covering)  WHERE diary_id=?

  This is needed because MySQL optimizer chooses PRIMARY KEY for PK lookups because the optimizer guesses PK lookup (unique lookup) is the fastest than any other type of index lookup, even though it is not true in this case. Using SQL hints is sometimes not easy, especially when you use O/R mapping tools.

  Personally I hope that InnoDB team or outside experts supports new InnoDB data format which is optimal for handling such large data types. PBXT handles these types of columns pretty well. PBXT stores large texts separately from other smaller columns, and it won't read large text data when SQL statement doesn't touch the large columns. If such kinds of data formats (storing specified columns in DDL into separated blocks) are supported in InnoDB, that will be nice.