MySQL: Does the order of the fields in a table make a difference in performance?

Question

Specifically I am wondering if I should place all my fixed length fields like int, timestamp, char at the beginning of the CREATE TABLE and place all the variable length like varchar at the end.

Also, I am wondering if TEXT fields are treated differently.

Answer 1

Depending on the RDBMS storage engine, it may reorder the fields internally for storage and display anyway. If we look at this question from the angle of a C program, a table in a database is like a C struct. E.g. A table in a c program might look like:

typedef struct _Table {
  char type,      /* 1 byte   */
  int id,         /* 4 bytes  */
  char *name;     /* 4 bytes  */
  char city[25];  /* 25 bytes */ 
} Table;          /* 34 bytes total */

You can see that in your program, you will expect that your first struct element is at the first pointer location, then one byte over will be your second element location, etc... These are called offsets.

Your RDBMS will likely store the file data structure in a c style struct so that it can understand what the offsets (memory locations) of data will be for each row. Then indexes will be applied to make searching amongst like groups of rows. An index location is simply a pointer to the first byte of each matching row (or struct).

As a software engineer type, you'd probably want to pack the struct so that the smallest data types are at the beginning of the struct so that as your pointer arithmetic scans over them you'll find conclusions faster, however this is a design decision.

Bottom line, best practice says to design your model smallest to largest, but it's probably being re-organized by the storage engine requirements anyway.

Answer 2

I would venture to say no up front in terms of the table. However, the order of fields in an index and the number of fields in a WHERE matter a great deal.

Example : You have the following table :

CREATE TABLE testtable
(
    a INT,
    b INT,
    c INT,
    KEY (a,b,c)
);

Note this query :

SELECT * FROM testtable WHERE a=1 AND b=2 AND c=3;

Since every column in the WHERE clause is mentioned as a eq_ref, aka equa-reference (using =), the index can be used to zero in on one column.

Note this query :

SELECT * FROM testtable WHERE a=1 AND b>2 AND c=3;

Column a is in front of the index as an eq_ref, but Column b is not. This is a range query. Depending on the cardinality of Column a (cardinality visible in SHOW INDEXES FROM testtable), an index scan if cardinality of a=1 is very low and the total number of rows with Column a=1 is less than 5% of the number of rows in testtable, otherwise a full table scan is chosen by any Query Optimizer (MySQL, Oracle, PostgreSQL, SQL Server, etc).

Note this query :

SELECT * FROM testtable WHERE b=>2 AND c=3;

This WHERE clause never mentions Column a. Result? automatic full table scan.

As far as order of columns in a table goes, defragging tables and making table formats with fixed row lengths could reduce any possible issues with table column order is that is a suspected concern.

If anyone knows of issues with Oracle, PostgreSQL, SQL Server, or other RDBMS's concerning table column order, please chime in.