The case for supplement tables in MySQL

As your database grows, you may start to notice that certain tables are becoming increasingly wide, with more and more columns. While this may seem like a convenient way to store all of your data in one place, it can actually slow down your database performance. This is because when a table is very wide, each row consumes more disk space, which means the rows are not packed together as efficiently on disk. To combat this, we can use a technique called a "supplement table" or a "meta table", which involves shuffling less frequently used columns off into a separate table to keep each row short and improve disk access performance.

What is a supplement table?

A supplement table, also known as a meta table or addendum table, is a secondary table that contains columns that aren't used very often. By breaking a very wide table into more focused groups of columns, you can optimize your database for faster read and write times. Of course, like any optimization, there are trade-offs. While supplement tables can help improve performance, they can also add complexity to your database design, making it harder to manage and update.

When to use supplement tables?

The decision to use supplement tables depends on the size and complexity of your database, as well as the specific needs of your application. A good rule of thumb is to consider using supplement tables when you have a very long table (lots of rows) that is also very wide (lots of columns), and there are many columns that are not used very often.

How to create a supplement table

Creating a supplement table is fairly straightforward. First, identify which columns are frequently used (i.e. the "hot" set of columns) and which columns are not. Then, create a new table containing only the "hot" column set, leaving the less frequently used columns in the original table. You can then join the two tables as needed using the ID column as a key.

For example, if you have a film catalog with a wide film table, you could create a film_narrow table containing only the title, rental_rate, and rating columns as the "hot" set of columns. The other columns, such as the description, could be moved to a film_addendum table. You can then join the two tables as needed using an inner join on the ID column.

SELECT
  *
FROM
  film_narrow
INNER JOIN film_addendum ON film_narrow.id = film_addendum.film_id

Potential issues with supplement tables

While supplement tables can improve database performance, they can also add complexity to your application design. You would have to change your queries on your application side to ensure you got back all the columns you needed. Additionally, when issuing updates you'd need to make sure you target the correct table.

Despite these potential issues, supplement tables can be an effective way to optimize your database and improve performance. By keeping your rows short and your hot column set focused, you can reduce your disk I/O and speed up your database access times.

Conclusion:

By shuffling less frequently used columns off into a separate table, you can keep your rows short and improve disk access times. While there are potential trade-offs, such as added complexity and the need for inner joins to reconstitute full rows, supplement tables can be a valuable tool for optimizing your database design.