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 removed the mysql tag for this question because comments on other RDBMS's could have different perspectives on table column order that no one thought about for MySQL. Nevertheless, this is an excellent question (+1) to bring to DBA.SE and other DB Gurus. – RolandoMySQLDBA Jul 14 '11 at 15:44

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.

| improve this answer | |
  • I think you forgot to add that it's also a little bit on the over-optimization side – vol7ron Sep 16 '11 at 17:50

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.

| improve this answer | |
  • You forgot to mention how the ordering of the where should be determined by the number of matches returned. That is, if c=1 returns 500,000 records and c=3 returns only 15 records, then c=3 should be placed first in the WHERE clause. – vol7ron Sep 16 '11 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.