I may be asking a silly question, but come on.
Does the order of columns in MYSQL interfere with performance in any way (either in writing or in research)?
Foreign keys are at the beginning or at the end, the fields type TEXT , BLOB and others of large volume are at the beginning or at the end, primitive types at the beginning of the table, finally ... columns in the table interferes with something ...
Thank you
giordanolima , your question is NOT silly.
The order of the columns may have rather BIG performance impact on some DBMS such as SQL Server , Oracle and MySQL .
This post can serve as a guide for future references. Some conventions may be adopted:
An example of a difference in performance is in the request of an index. The database system finds the row based on some conditions of the index and returns the row address. Now, let's say you're looking for "Idade" and this is in your table:
Id int,
Nome varchar(100),
Idade int
The system needs to find where Idade starts, because Nome has an indefinite 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. So the order can have a considerable impact. Yes .
There is no performance interference in table ordering, because even if you keep in mind something large like bigdata usually distributed on nodes (clusters) that maximizes processing, the database is designed to store read data and independent of the format and order. your question suggests a very long answer, more in short, does not interfere.
Some things for more information.
Bank Performance
Link 2
link 3
I made a small test to check the influence on the order of tables by creating and deleting and creating tables of several shapes, inserting, deleting and reading information follows the result:
we can note the following:
some ways of creating the table take more or less milliseconds to be created, but when repeated it varies more and less for these milliseconds (it becomes evident that the greater strong> come from HARDWARE and not logic)
then I made insertions and read and data according to the order of the tables that were created, and it is also possible to perceive the same difference again related to hardware performance, lastly (highlighted in blue) is what really matters, how long the bank takes to return the values, in the three forms I tried, the search was instantaneously taken less than milliseconds (obviously that were not 0.000 sec's, there was indeed a time, however it is irrelevant and MySQL returns as 0). what we have is a Costume to do things, but in practice, it's not like that ...