Does anyone know if there is a difference in bank performance if I create the FKs before the other fields in the table or vice versa?
I heard comments that it is ideal to always leave FK's as the first columns of the table, but I have not found any articles about.
According to comments, I have no knowledge of the subject, and never took time to measure performance investigating this type of detail. However, the In SOE has answers that lead us to believe that column order may impact performance recovery of data in some DBMSs, including MySQL.
The reason for this, according to proponents of this theory, is that columns of variable size (eg
VARCHAR ) can make it difficult to retrieve information in later columns, since MySQL (like some other DBMSs) needs to go through columns in the registry to find the offset up to the searched column. As this response the impact can reach 17% in a 20-column MySQL table.
On the other hand I always take that kind of statement with a bit of caution. In addition to SGDBs doing all sorts of data storage and query optimization, they are constantly evolving, so this impact may not be as significant. In terms of performance I believe that reordering columns is at least an early optimization. I worry much more about indexes, execution plans, locks , etc.
That said, I have always adopted a convention that meets the best practices suggested in the answers. I try to position Primary Keys first, Foreign Keys in the sequence and then the other columns. I do this for organizational issues, but if this comes with a better performance bonus :).
The chosen answer for the question mentioned above goes even further by suggesting the following order: