MYSQL - Column order and performance

Question

I may be asking a silly question, but come on.

The order of the columns in the MYSQL interferes something in terms of performance in some way (whether in writing or in research)?

Foreign keys are at the beginning or end, the type fields TEXT, BLOB and others of great volume are at the beginning or the end, primitive types at the beginning of the table, anyway... how far the order of the columns in the table interferes something...

Thank you

Answer 1

giordanolima, your question is NOT silly.

Column order can have yes GREAT impact on performance in some DBMS such as SQL Server, Oracle and MySQL.

This post can serve as a guide for future references. Some conventions can be adopted:

1. Primary key first
2. Foreign key according to
3. Columns searched frequently third
4. Columns updated frequently quarter
5. Columns with "null" allowed" by last

An example of difference in performance is in the request for an index. The database system finds the line based on some index conditions and returns the line address. Now, say you’re looking for "Idade" and this is on your table:

Id int,
Nome varchar(100),
Idade int

The system needs to find where Idade begins, because Nome has undefined position. However, if you change the order to:

Id int,
Idade int,
Nome varchar(100)

Now the system knows that Idade was found 4 bytes after the beginning of the line. Then, the order can have a considerable impact yes.

Answer 2

performance interference does not occur in the ordering of tables, because if you have in mind something large voluminous as bigdata generally distributed in nodes(clusters) that maximize processing, the database is designed to store read data and tables independent of format and order. your question suggests a very long answer, more summarizing, does not interfere.

some things for more information.
Performance Banco
Link 2
link 3

I made a small test to check the influence in the order of the tables creating and deleting and creating the tables of varied 3 ways, inserting, deleting and reading information, follows the result: we can note the following:
some ways to create the table take more or less milliseconds to create, but when repeated it varies for more and less these milliseconds(it is evident that the interference greater comes from the HARDWARE and not of logic) then made insertions and reading and data according to the order of the tables that were created, and it is also possible to notice the same difference again related to hardware performance, last (highlighted in blue) this what really matters, the time it takes the bank to return the values, in the three ways I tried, the search was instantaneous taken less than milliseconds (obviously it was not 0.000 sec’s, existed in fact a time, however is irrelevant and Mysql returns as 0). what we have is a call Custom to do things, more in practice, it’s not like that...