Query speed based on order of columns

Question

Does the order of the column types in your database have any affect on the query time?

For example, would a table with mixed ordering (INT, TEXT, VARCHAR, INT, TEXT) be slower to query than a table with consecutive types (INT, INT, VARCHAR, TEXT, TEXT)?

Answer 1

The answer is yes, it does matter, and it can matter a great deal, but usually not much.

All I/O is done at a page level (typically 2K or 4K depending on your OS). Column data for rows are stored next to each other, except when the page becomes full, in which case the data is written on the another (usually the next) page.

The greater the on-disk data space required for columns between (based on the the table definition) the columns you select, the greater the chance that the data for the selected columns will (sometimes) be on different pages. Being on a different page may result in an extra I/O operation (if there are no other rows being selected on the other page). In the worst case, each column you select could be on a different page.

Here's an example:

create table bad_layout (
num1 int,
large1 varchar(4000),
num2 int,
large2 varchar(4000),
num3 int,
large3 varchar(4000)
);

create table better_layout (
num1 int,
num2 int,
num3 int,
large1 varchar(4000),
large2 varchar(4000),
large3 varchar(4000)
);

Comparing: select num1, num2, num3 from bad_layout; select num1, num2, num3 from better_layout;

Because for bad_layout each num column is basically going to be on a different page, each row will require 3 i/O operations. Conversely, for better_layout num columns are usually going to be on the same page.

The bad_layout query is likely to take about 3 times longer to execute.

Good table layout can make a large difference to query performance. You should try to keep columns that are usually selected together as close as possible to each other in the table layout.