Home / server / Questions / 195207
In Process
Tim Murphy
Asked: 2010-10-27 22:44:31 +0800 CST

Does the order of creating a columns for a table impact performance?

  • 772

For purely asthetic reasons I've always had the first column(s) of a table as the primary key column(s). After that I've not taken any care to the order columns are added to the table. Is this wrong?

Is there a performance benefit to place say integer columns in the table before text or binary columns? Or maybe indexed columns first?

While the current database I'm working with is MySQL answers for other databases would be helpful.

sql-server mysql database database-performance

3 Answers

  1. Is this wrong?

    No, I do the same - mostly because a table always starts with the PK.

    Is there a performance benefit to place say integer columns in the table before text or binary columns? Or maybe indexed columns first?

    Not on SQL Server. If it is on MySQL it would be a screwup of pretty major proportions unless the overhead is REALLY small. Indices live separate of the tables. Over the lifetime of a database, indexed columns and even existing columns can change.

    • 1

  2. Actually, for really hot loads, it can have significant impact. If you look at the Anatomy of a Record you'll see that columns follow the record header, fixed column first followed by variable length ones. So whenever a column is accessed, the record header will have to be accessed first and this access is almost always an L2 cache miss. Any subsequent access within the same cache line (64 bytes) will be an L2 cache hit almost 100% of the times. Given that the CPU cycle difference between an L2 cache miss and a hit is about 2 orders of magnitude, you get a pretty big performance boost if you arrange your frequently accessed columns near the record header. The end-to-end performance boost will not be anywhere 2 orders of magnitude, but for certain OLTP loads it can add up to 5-10% overall. For analytic loads, the cost of IO overwhelms everything else and you probably won't be able to measure any difference.

    This logic gets applied to every index individually, but on indexes you have to consider that the order of declaring the index is the actual order of the key so you don't have much room for change.

    • 1

  3. I'd say on SQL Server it doesn't matter. SQL Server reads in entire pages and I'm not sure the processing to get to the 3rd column on a page is more than the 2nd or 5th or anything else.

    Doesn't matter for the table, but it does for the index. The first column in the index must be in the WHERE clause for the index to be used.

    • 0