Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

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.

share|improve this answer
    
Thanks for the answer. Frankly I don't care what language you use but others might. How about stuffup instead? –  Tim Murphy Oct 27 '10 at 7:19
    
Fuckup is in the dictionary. It is not marked as vulgar. It means embarrasing mistake. It has no relation to a sexual activity. Anyone not fine with that better learns english and realizes that language is not defined by ones onwn illusions. Oh, and that langauge incompetence only hides real incometence. Political correctness, on the price of being always wrong, is not good. As seen as in recent politically correct US foreign policy (in reference to wikileaks). Fuckup is harsh. It matches the size that mistake would have. –  TomTom Oct 27 '10 at 7:29
5  
Vulgarity isn't necessarily the point, professionalism is. Doesn't really matter here, but it does in many cases. Nothing to do with "political correctness". –  phoebus Oct 27 '10 at 14:42

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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