Is there any reason to worry about the column order in a table?

by

Column order had a big performance impact on some of the databases I’ve tuned, spanning Sql Server, Oracle, and MySQL. This post has good rules of thumb:

  • Primary key columns first
  • Foreign key columns next.
  • Frequently searched columns next
  • Frequently updated columns later
  • Nullable columns last.
  • Least used nullable columns after more frequently used nullable columns

An example for difference in performance is an Index lookup. The database engine finds a row based on some conditions in the index, and gets back a row address. Now say you are looking for SomeValue, and it’s in this table:

 SomeId int,
 SomeString varchar(100),
 SomeValue int

The engine has to guess where SomeValue starts, because SomeString has an unknown length. However, if you change the order to:

 SomeId int,
 SomeValue int,
 SomeString varchar(100)

Now the engine knows that SomeValue can be found 4 bytes after the start of the row. So column order can have a considerable performance impact.

EDIT: Sql Server 2005 stores fixed-length fields at the start of the row. And each row has a reference to the start of a varchar. This completely negates the effect I’ve listed above. So for recent databases, column order no longer has any impact.

More Related Contents:

  1. MySQL: Return 2 different result together
  2. Get top n records for each group of grouped results
  3. Insert into a MySQL table or update if exists
  4. need to return two sets of data with two different where clauses
  5. WHERE vs HAVING
  6. quick selection of a random row from a large table in mysql
  7. Join tables with SUM issue in MYSQL
  8. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  9. Return default result for IN value regardless
  10. How can I search (case-insensitive) in a column using LIKE wildcard?
  11. Search for “whole word match” in MySQL
  12. sql joins as venn diagram
  13. How can I join multiple SQL tables using the IDs?
  14. Maximum number of records in a MySQL database table
  15. MySQL, better to insert NULL or empty string?
  16. SQL: Repeat a result row multiple times, and number the rows
  17. SQL: selecting rows where column value changed from previous row
  18. Why do results from a SQL query not come back in the order I expect?
  19. How to generate data in MySQL?
  20. MySQL combine two columns and add into a new column
  21. Is merge statement available in MySQL
  22. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  23. Mysql covering vs composite vs column index
  24. MYSQL syntax not evaluating not equal to in presence of NULL
  25. MySQL: LAST_INSERT_ID() returns 0
  26. Calculating a Moving Average MySQL?
  27. INSERT INTO or UPDATE with two conditions
  28. Calculating time difference between 2 dates in minutes
  29. MySql. How to use Self Join
  30. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci

Leave a Comment