4

I'm working on an email support system. Each stored email contains from_email, subject, date, some flags, and the contents of the mail. Clearly, the amount of mail can be large, and our system is mainly focused on the most recent messages (for instance, the last 14 days) and the older ones are considered archived. We do also want to search, filter on some tags, and so on.

For list views of the emails, we do not need to consider the content field. I am considering two options, all data in one table, and a seperate table which stores the LONGTEXT email bodies.

Assuming that the SQL SELECT fields does not contain the content field, is it more efficient to have it in a seperate table?. Clearly, the LONGTEXT field is not stored with the fixed-length row data, but I imagine it could be interleaved, so that the number of pages that must be fetched for a list view is larger.

I'm using MariaDB 5.5.25 and the InnoDB engine.

apartridge
  • 1,790
  • 11
  • 18

1 Answers1

4

I found this information in the MySQL 5.5 manual, section 14.3.12.2. File Space Management

If [an InnoDB] row is less than half a page long, all of it is stored locally within the page. If it exceeds half a page, variable-length columns are chosen for external off-page storage until the row fits within half a page. For a column chosen for off-page storage, InnoDB stores the first 768 bytes locally in the row, and the rest externally into overflow pages. Each such column has its own list of overflow pages. The 768-byte prefix is accompanied by a 20-byte value that stores the true length of the column and points into the overflow list where the rest of the value is stored.

In plainer English, if you create a table with just a primary key and a longtext, InnoDB will split the row if the longtext is greater than 8000 bytes (half a page).

My suggestion would be to put the longtext at the end of your stored email table row, since InnoDB will probably split the row for long emails anyway.

It's good database practice to put all of your fixed length columns in the beginning of your row, and your variable length columns at the end of your row.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • Thanks for your answer. Based on this information, I would find it better to have another table with the body, so that the body contents field does not take up space in the `meta` table ? – apartridge Jul 10 '13 at 13:56
  • 1
    I would also mention this [5.1. Overview of InnoDB Row Storage](http://dev.mysql.com/doc/innodb/1.1/en/innodb-row-format-overview.html) `The storage for rows and associated columns affects performance for queries and DML operations. As more rows fit into a single disk page, queries and index lookups can work faster, [...].` – t.niese Jul 10 '13 at 13:57
  • 1
    Note this is different for tables in files of type Barracuda. When row needs to split, only 20 byte length and pointer are stored in-row, the rest (even if less than 768bytes) is stored externally. http://dev.mysql.com/doc/innodb/1.1/en/innodb-row-format-dynamic.html – Z.T. Jan 20 '15 at 16:33