2

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?

closed as too broad by Philᵀᴹ, RDFozz, Andriy M, hot2use, McNets Apr 10 '18 at 9:13

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 450+ is definitely bad design. I suggest you to read Normalisation properly then ask same question to yourself. – KumarHarsh Apr 9 '18 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 Garg Apr 9 '18 at 6:25
  • 1
    Impossible to answer without knowing the structure of the data & whether it can be normalised / split – Philᵀᴹ Apr 9 '18 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érace Apr 9 '18 at 9:52
  • 1
    Having 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 M Apr 10 '18 at 8:40
2

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. – Shilpa Garg Apr 10 '18 at 10:45
  • The default page size is 16KB; 5K will fit. – Rick James Apr 30 '18 at 1:13
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.