Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jan 16 '12 at 15:27

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 4 down vote accepted

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

share|improve this answer
    
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

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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