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?
Asked
Active
Viewed 177 times
1
-
3What kind of table? Html table? Lua table? Dinner table? – Oct 23 '09 at 13:22
-
It would improve performance if you requested photos of a particular type. If you have 30 million rows to be queried and you return 10 million of the type you wanted that's ok. If you can just return all rows from one table and remove the where clause this is quicker. You really must consider the other problems that can arise like when you want all photos for a particular user. You would need 3 queries, have 3 results sets and have to compile them. You could use a union but only if all columns are the same so then you'd argue to store in one table again. Catch 22 :D – Robert Oct 23 '09 at 13:30
-
It's impossible to say without knowing what platform you are on, and a bit more of what you mean by "type". Also it matters whether you store the photo on the table in binary, or just a pointer to a file. Without that it's hard to say, but it's "unlikely" to be much of a benefit. – Russell Steen Oct 23 '09 at 13:40
-
Normalization is done to keep errors from creeping in and to prevent the duplication of data. If you are concerned about those look into: http://en.wikipedia.org/wiki/Database_normalization – jjclarkson Oct 23 '09 at 14:58
7 Answers
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.
JonH
- 32,732
- 12
- 87
- 145
1
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.
Michael Dillon
- 31,973
- 6
- 70
- 106
0
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.
Matt Wrock
- 6,590
- 29
- 23
0
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!).
karim79
- 339,989
- 67
- 413
- 406
0
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.
GaryDevenay
- 2,405
- 2
- 19
- 41
0
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.
exhuma
- 20,071
- 12
- 90
- 123
0
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.
Kevin Peno
- 9,107
- 1
- 33
- 56