2

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.

5

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...

1
  • Thanks for your thorough answer. I never looked at it from the point of view of a gap that occurs between inserting in table 1 and table 2. I will keep it simple (stupid) heh. – LedZeppelin Jan 16 '12 at 11:07
6

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.

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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