Performance difference in creating FK Mysql

Asked

Viewed 191 times

0

Can anyone tell if there is a difference in bank performance if I create FK’s before the other fields in the table or vice versa?

I heard comments that the ideal is to always leave FK’s as the first columns of the table, but I did not find any article about.

mysql performance

  • To make it clear. You are asking if the order of the fields in the create table has some influence on performance, is that it? Changing into kids, if having a field like FK in the second position of create table is better than having the same field as FK in fifth position (considering the same indices and all other equal factors)?

  • If that’s it, I found this question in SOE with responses suggesting variable size fields (style VARCHAR) in the middle of the table may impact the performance when retrieving the data. The accepted Answer suggests that PK and Fks are the first fields in the table. Personally I find the topic controversial when you have indexes and Engines making all kinds of optimization. But I always try to put the fields of PKs and FKS first (it became a convention of mine).

  • That was my question, it really makes sense. Put it as an answer so I can mark it as answered. @Anthonyaccioly

1 answer

0


According to comments, I have no knowledge of the subject, and have never taken the time to measure performance investigating this kind of detail. However, the question "Is there any Reason to Worry about the column order in a table?" in SOE has answers that lead to believe that the order of the columns can impact the performance data recovery in some Dbms, including Mysql.

The rationale for this, according to the proponents of this theory, is that columns of variable size (e. g. VARCHAR) may make it difficult to retrieve information in later columns, since Mysql (like some other Dbms) needs to scroll through the columns in the registry to find the offset up to the searched column. As per that answer the impact can reach 17% in a Mysql table with 20 columns.

On the other hand I always take that kind of statement with a little caution. In addition to Dbms doing all sorts of optimization in data storage and query, they are constantly evolving, so this impact may not be as significant. In terms of performance i believe reordering columns is at least an early optimization. I care much more about indexes, execution plans, Locks, etc..

That said, I have always adopted a convention that goes against the best practices suggested in the answers. I try to position Primary Keys first, Foreign Keys in sequence and then the other columns. I do it for organization reasons, but if it comes with an even better performance bonus :).

To chosen answer for the question mentioned above goes even further suggesting the following order:

  • Columns of Primary key first
  • Columns of Foreign Keys in sequence
  • So frequently searched columns
  • Then frequently updated columns
  • Columns nullable lastly
  • Columns nullable less used after columns nullable often used

Browser other questions tagged mysql performance

You are not signed in. Login or sign up in order to post.