MySQL / Rails Performance: One table, many rows vs. many tables, less rows?

Question

Hi,

In my Rails App I've several models dealing with assets (attachments, pictures, logos etc.). I'm using attachment_fu and so far I have 3 different tables for storing the information in my MySQL DB.

I'm wondering if it makes a difference in the performance if I used STI and put all the information in just 1 table, using a type column and having different, inherited classes. It would be more DRY and easier to maintain, because all share many attributes and characteristics.

But what's faster? Many tables and less rows per table or just one table with many rows? Or is there no difference at all? I'll have to deal with a lot of information and many queries per second.

Thanks for your opinion!

Answer 1

The only way to know for sure is to try both approaches and measure the performance.

In general terms, it depends if you're doing joins across those tables and if you are, how the tables are indexed. Generally speaking, database joins are expensive which is why database schemas are sometimes denormalized to improve performance. This doesn't usually happen until you're dealing with a serious amount of data though i.e. millions of records. You probably don't have that problem yet and maybe never will.

Answer 2

Many tables and fewer rows is probably faster.

That's not why you should do it, though: your database ought to model your Problem Domain. One table is a poor model of many entity types. So you'll end up writing lots and lots of code to find the subset of that table that represents the entity type you're currently concerned with.

Regular, accepted, clean database and front-end client code won't work, because of your one-table-that-is-all-things-and-no-thing-at-all.

It's slower, more fragile, will multiply your code all over you app, and makes a poor model.

Do this only if all the things have exactly the same attributes and the same (or possibly Liskov substitutable) semantic meaning in your problem domain.

Otherwise, just don't even try to do this.

Or if you do, ask why this is any better than having one big Map/hash table/associative array to hold all entities in your app (and lots of functions, most of them duplicared, cut and paste, and out of date, doing switch cases or RTTI to figure out the real type of each entity).

Answer 3

Lots of tables with tiny number of rows is bad, and few tables with lots of rows is bad. bst to have a balance. I would suggest that you seek to keep the max number of rows per table pretty much equal to how many tables you have. You've got a crazy program to write anyway, so you might aswell make it a little bit crazier to get nice efficient performance.

Good luck chap