Should I break 1 table out to many if it will be hit often?

Question

Right now I have a table for Photos. I expect this table to be hit a lot. Will I improve performance if I break it out into 3 tables if, for exmaple, I have 3 different types of photos? Or will this not improve performance?

Answer 1

Why would you want to break this table out into three different types of tables given a photo is simply a photo. It sounds like you want a category field rather then introducing additional tables.

It will not improve performance, it will be however

• a nightmare to manage
• will cause you to write spaghetti code
• will make reporting or additional functional calls a mess

For instance assume you went your route and created Three tables: Scenery, Portraits, and Entertainment and you loaded photos into these tables. What happens when you add another category, are you going to add another table? I hope not. Keep them all in one table. Add an index to the table (the pk). Add a category to the table to categorize the photo.

Answer 2

if "hit" means reads and especially if the reads are using a primary key lookup, keep them in a single table.

breaking the table out into many tables helps when you have a lot of data and your queries often use a key that can be partitioned. For example, if you typically query by date range, splitting tables by month might help because the query can optimize by only selecting from tables that contain the queried range. However, there are better ways to accomplish efficiencies here by using partitioning and not splitting to separate tables.

Having less data for a query to read always increases performance, but this will not have much of an impact on simple key lookups. I bet your lookups on this table will be simple key lookups (I could be wrong) and if so, keep it simple in one tabls.

Answer 3

I would go the one table approach with a type column that holds the typeid or name of your photo types. Your suggestion would mean adding/removing/modifying tables on your database should you decide to make amendments to your photo types (e.g. adding a new type would involve creating a new table. yuck!).

Answer 4

You don't say what type of database you are using. Is it an SQL relational database or one of the non-SQL datastores such as Hadoop, CouchDB, Redis or Tokyo Cabinet?

For storing photos, I would choose one of the non-SQL datastores and not worry about breaking out different types of Photos into different tables.

Answer 5

I Highly doubt that your table will be "hit" enough times to decrease your database performace. I would keep it as one table and as above add a "Type" column. You shouldn't have any problems with that.

Answer 6

I am also with the idea to keep them in a single table and add a discriminator field. What others failed to mention: Add an index to that field!

If (and only if) your selects are filtering on the indexed field(s), performance won't suffer much.

As an additional bonus, you can make some ORMs use this discriminator field to do inheritance in your code. In other words, in your app-code you have a nice OO-inheritance model, while being able to use it on pretty much any DB backend.

Answer 7

I wouldn't write it this way initially. However, as many have mentioned, I would add a category column. This will allow you to partition the table in the future. Here's some information on table partitioning in MySQL.

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html