Should I split a single table with an AUTO_INCREMENT PRIMARY KEY into two tables that can be joined on that PRIMARY KEY?

Question

I have a table that has 46 fields with an AUTO_INCREMENT PRIMARY KEY. It's hard to look at a table that has this many columns, so I frequently use a SELECT statement to look at 34 of the fields that I mentally categorize as 'group 1' and 12 fields that I categorize as 'group 2'.

Would I face a performance hit on reads/writes if I split this table into two tables, one with 34 fields and another with 12 fields.

Both would have the same AUTO_INCREMENT PRIMARY KEY so that they could be joined on the PRIMARY key if I need to look at at 46 fields at once.

Answer 1

If there is a 1:1 mandatory relationship then you don't add much value by splitting the table. There are cases were 1:0..1 relations are best as separate tables though.

And your keys would go out of synch quickly. Assuming you'll insert into both tables in a single transaction, a failure on the first insert would generate a gap that doesn't happen on the 2nd table.

If you had an AUTO_INCREMENT on one table and used LAST_INSERT_ID, then you lose the ability to do multiple row INSERTs

The KISS principle applies...

Answer 2

It's hard to look at a table that has this many columns, so I frequently use a SELECT statement to look at 34 of the fields that I mentally categorize as 'group 1' and 12 fields that I categorize as 'group 2'.

Create a view.

Answer 3

It's difficult to give an answer without knowing what the columns are. If the table is properly designed (normalized properly), then having many columns isn't a bad thing. You can use views as suggested, or you can just select the columns that you need to see based on the reason you are doing a query.

My guess, though, is that if we saw the columns, we'd find some cases of normalization issues since you already naturally want to see only part of them. That's just a guess, though, not a design rule or anything like that.