The number of columns does not affect performance by itself. Of course, more data there is a higher cost, but it’s not even proportional, it’s amortized, so the more data you’re dealing with, the trend (exception) is for overall performance to improve a little in various scenarios. The cost to bring two data tends to be less than twice the cost of bringing a data (disregarding the data size difference).
If you have to deal with a lot of data then deal, no problem, the databases were made for this. If you have data that you shouldn’t handle, which is very rare for someone to do something like this, then you should improve it.
Splits into several tables is making a pseudo normalization and then it is almost certain that it will have performance fall, maybe even very big because it will have to make a join shape or other. You can see more about:
Then you’ll start doing one-on-one relationships needlessly, slowing everything down. It’s the opposite of what you imagine. The data needs to be picked up in different places instead of being picked up together. It should be obvious that even a bricklayer knows that picking up a brick in each place is worse than picking it all up together, but for some reason programmers don’t know.
Note that there is a myth about bringing all fields to be slower. Yes, that’s usually true, but it’s not that simple, and most people make simplifications. This may not be true, or be quite the opposite, it depends a lot on the scenario. See Why using "SELECT * FROM table" is bad?. Reading this you can see that the question is complex.
Either way, it changes a lot less than you think, and will only be relevant in extreme cases. In general the person has a ridiculously small database and thinks it has one of the largest databases in the world and that it needs absurd optimizations.
The decision of where to put the data should be primarily logical, do what is right conceptually, this alone should already give a good performance. In cases where there really is some real problem, and not imaginary, then one should think of an organization out of the ordinary, thinking about optimization and obtain extraordinary gains.
60 columns is quite low, really has many cases to store all this or more. The size of these columns will affect much more than the amount of them. But as said before, just because it has more data. Split does not decrease the amount of data.
It seems that the problem asks for fields together in a table for logical and performance reasons.
I think the number of columns does not significantly affect performance, as long as you select only the columns you need, instead of
SELECT * FROM ...agoSELECT col1, col2 FROM...– Miguel
60 columns? It seems to me a lot, nor is the performance point itself, but your table is probably denormalized, I can not think of a simple entity that has so many columns like this, should review it from the modeling point of view. The database has mechanisms to be relational, that is, allow you to relate a lot, but honestly I was curious, which 60 columns would be those that need everything at once in a single query? can you ask the question? can you help with the analysis
– Ricardo Pontual
I don’t know if it would help to put in the question, this table comes from a JSON that I take from a webservice, it comes with "subchaves" where I dismember in other tables, I try to maintain the structure that comes from them to not have problems when changing something, only in the main have about 50 fields, There’s more to my control fields, which means it’s already born big, and I have no control over it, and dismembering would be chaos at my base. My question is to get an idea if I should insert my fields in the same table, I think if fragmenting too much can get slow.
– Marcelo
Mysql 10.x? Are you sure? The latest version is not 8.x?
– anonimo
On my Server it looks like this: Mysql version: 10.2.36-Mariadb
– Marcelo