I have table having 450+ columns. Can you please confirm is this fine or should I need to split this table?
If yes, what value will be recommended for the number of columns per table in MySQL?
6
-
450+ is definitely bad design. I suggest you to read Normalisation properly then ask same question to yourself.– KumarHarshApr 9, 2018 at 6:23
-
As per my understanding, this is not good practice to implement too many columns. So can you please suggest what will be standard limit for columns in table @KumarHarsh– Shilpa GargApr 9, 2018 at 6:25
-
2Impossible to answer without knowing the structure of the data & whether it can be normalised / split– PhilᵀᴹApr 9, 2018 at 8:53
-
Can you provide DDL for these tables (you don't need to provide every line) and the scenario under which you feel you need this many fields per table? You might want to look at my answer to a question of a similar nature here. How's your performance for your own particular needs?– VéraceApr 9, 2018 at 9:52
-
1Having a table with 450+ columns is definitely uncommon, but whether it's a good idea or a bad one in your case is too broad a question without more details.– Andriy MApr 10, 2018 at 8:40
|
Show 1 more comment
2 Answers
The technical limit on the number of columns depends on the engine. InnoDB allows 1017 in MySQL5.7 Those 1017 columns can cover a maximum of 65535 bytes. Records are stored on "pages". InnoDB allows the page size to be configured to 4, 8, 16, 32, 64Kb. Your record must fit on a page so you can't stick a 5K record on a 4K page.
The problems with having wide records is that when the DB engine retrieves records it does so in pages. You can get few wide records on a page so retrieval performance decreases. DBs pull the results through memory so subsequent retrievals will see if the data remains in memory before falling back to storage. Having many records on a page means that the first physical retrieval of a page is more likely to load into memory records which can be logically (and much faster) read from memory.
From a design perspective it depends on what your use case is. In an OLTP system then I would feel uncomfortable with 450+ columns. A database is not a dumb store. It can be used to enforce rules on the structure of information and the relationships between different data entities. This is an incredibly powerful weapon to have in your arsenal.
In a data warehouse supporting certain analytical systems 450+ sounds like a lot however I have seen some wide denormalised tables used to feed OLAP cube technologies.
If I saw a 450+ column table I should also ask questions about security. When I grant access to that table do I want everyone with access to have access to all 450+ columns? In addition to storage efficiency/performance normalisation can also factor in a security design.
Consider performance. Of those 450+ columns which ones get retrieved the majority of the time? Do you really want to have the expense of retrieving 450+ columns if only 32 are used on a regular basis?
The answer I have given assumes that InnoDB (the default) is used.
-
Thanks Dave for sharing the information. Yes, Our system is OLTP. I also want to normalized these tables. This is custom data table. I will try to split columns in multiple tables. "Records are stored on "pages". InnoDB allows the page size to be configured to 4, 8, 16, 32, 64Kb. Your record must fit on a page so you can't stick a 5K record on a 4K page." This information will help me during splitting the columns. Apr 10, 2018 at 10:45
-
Dave's answer is good, I would like to add that there is a big difference between OLTP and data warehouse. If the database is used for DWH, having many columns in the same table will save a lot of JOINs, which is good. In that case, you typically have a small number of queries and they are big. Writes are scheduled and you probably don't care much about their performance.
