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

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.

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.

StackExchange Database Administrators Q#3838, answer score: 2