Does the Size of Unselected Columns Impact Performance of SELECT Queries in MySQL?

Question

Suppose I have a table with the following structure, overly simplified for example purposes:

Table "Persons":

CREATE TABLE Persons (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,  
    address VARCHAR(255) NOT NULL,  
    telephone VARCHAR(255) NOT NULL,  
    biography LONGTEXT,
    description LONGTEXT,
    PRIMARY KEY  (id)
)

Suppose I have 1000 entries in the "People" database, and that Biography and Description can have up to a few MBs in size.

In the following example query, where I'm selecting all Persons and reading only small data, does biography and description columns columns affect my query performance, even if I don't include them in my SELECT query?

SELECT name, address, telephone FROM Persons;

Would it be best to move biography and description to a dedicated table to avoid the performance penalty, if any?

Answer 1

Does the Size of Unselected Columns Impact Performance of SELECT Queries in MySQL?

It depends.

All rows of a table are stored physically on disk in groups known as data pages. By default, a data page is 16 KB big in MySQL. Whenever you query a table, all of the pages that contain the data you ask for, need to be located and loaded off disk. Columns and rows that your query doesn't ask for, may happen to live in the same data page as ones your query does ask for. Obviously loading more data off disk (i.e. a more full data page) will take longer to do. But it's generally rather negligible because 16 KB for a data page is pretty granular. The overhead is fairly small.

The exception to this is for larger rows and columns of data, they're typically stored "off-row", meaning outside the data page that the rest of the row is in. Generally speaking, there's some kind of pointer stored in the page instead that links the two back together. So, when your query requests those data pages, those really large pieces of off-row data don't need to be loaded off disk if your query doesn't ask for them.

Would it be best to move biography and description to a dedicated table to avoid the performance penalty, if any?

Not necessary when they meet the threshold to be stored off-row. That's what the database system is essentially automating for you, at the disk level, as discussed in my previous paragraph.

One use case where storing those larger fields in their own table could improve performance of the workflow in other ways is when it comes to locking with concurrency and DML queries (e.g. UPDATEs and DELETEs). If your main table is read from a lot and the large fields are updated equally frequently, you may run into locking contention, since updating a column generally locks that whole row. By separating the larger fields that change frequently into their own table, you eliminate locking against the rest of the row data that's being SELECTed against (assuming the SELECT query isn't utilizing those larger columns in that moment).

Answer 2

If you are never doing select *, then only the selected fields are fetched, and the size of unselected fields has a very minor impact.

Of course, the speed of fetch is still dependent on other factors such as indexes, triggers, hardware spec, internet speed etc.