Django/SQL: what's most efficient, more columns or more tables?

Question

I have a MySQL database that I'm using with Django. One of my tables has around 60 columns. I'm wondering whether to split it into 5-6 smaller tables. This would make logical sense, since the columns split nicely into 5-6 logical groups.

The downside would be that some of the Django pages would then require 5-6 row queries instead of 1.

Is it more efficient to have one table with many columns, or many tables with fewer columns? If the former, how much of a disadvantage is it to have many tables? (as far as one can quantify such things...)

Thanks for your advice :)

Answer 1

1. Use old Opccams' razor. Do not do unnecessary moves. Are you okay with your one table? If so - leave it as is. Do not make yourself a trouble out of nowhere.

2. You are wrong about 6 queries. It will be still single query. But see item 1.

Answer 2

Take a look at

http://en.wikipedia.org/wiki/Database_normalization

http://databases.about.com/od/specificproducts/a/normalization.htm

Answer 3

If it's logical to split them into multiple models, then split them. Just for reason of efficiency, don't keep them in single model.

Performance/Effectiveness of retrieving data really depends on how you structure your query. No point loading all 70 columns in memory when you will be using only 5 - 10 fields. You can just select what you want using .values().

Also when you split into multiple models and using foreign keys to relate them, then using select_related you can retrieve same information with fewer queries and sometimes even 1.

If we can see model, may be we can give our best opinions.