'Long' MySQL tables or multiple tables?

MattG

I have a table which lists people and lots of details about them (i.e. hair colour, eye colour, tattoos, date and place of birth, daily, weekly and overall rank) and I want to add some extra rows.

In total the 'people' table currently has 26 fields and with the additions it would probably be closer to 30. The fields are mainly varchar and unsigned smallint with a few others a, half of them are regularly fetched from the database for display in PHP pages and about half a dozen are regularly updated (in particular the daily/weekly/overall counters & ranks).

To get to the point, is my current table design inefficient with all of those fields in one table and would it be better to break them up into multiple tables or keep them all in one 'long' table?

This is probably a simple answer but database design isn't my strongest point.

Thanks

Bjom

First of all fields are columns. Records are rows. So you are worrying about the 'width' of your table much more than its 'length'.

The number of fields doesn't matter THAT much and going from 25 to 30 is not a big change either, nor is this a huge table.

You can break up the table into two parts, if some fields have really much much more traffic than the others.

But there is a trade off that you need to be aware of: You create overhead with the breaking up, because now you need to link those two tables with a key and you have to join them when retrieving the less frequently used fields' data.

Most likely it won't boost your performance, but of course the best way is: try it! Profile your DBs performance, break up the tables and see which setup runs faster - and how much...

Bjom

MattG

Thanks Bjom

CoderDan

It really depends on the uniqueness of the other fields.
When your DB is getting towards the size you expect it to be (or just 'big') look at the 'uniqueness' of the data vs the size it takes up, and base other tables (or not) off that.
I'll give you a extreme example, just to illustrate:
lets say we have a table:
id (int primary key)
name (varchar 32)
company (varchar 250)
and you then enter in every employee of microsoft, amd, intel and, um, walmart.
you end up with a few million entries, and the id will be unique, the name will have perhaps 1 million unique entries, and the company will have... 4 unique entries.
This means make a 'companies' table, with 4 entries, and an int primary key (could probably even be a small int, depending how many companies you plan to add) and then change the employees table to be:
company( int )
and link it up to the new table.
of course, if you only are ever going to have a few hundred or thousand entries in your table, and all fields are fairly small, then there's not really going to be much gain from splitting it out (or much loss if you split it out unnecessarily) so it's not something to worry too much about.
And if performance is an issue, and the data is large, then do the profiling suggested by Bjom regardless of what anyone advises, and probably also look at mysql setup on teh server, how much memory is allocated for what DB type, etc, and you can get even more performance out there.
(As a side note, we're currently moving our site to a new server cluster, with 2 servers purely running mysql, one as a master, the second as read only, synced together, optimized for innodb, used by 5 other servers, 3 load balancing the main site, 1 running https, and one for development, and to be honest, that alone gives me a headache 😛)