I have a table with about 100 attributes, with id as the primary key and all the other attributes are related to the id. Out of these 100 attributes, the application needs about 15 attributes most of the time. And the mostly used select query will have about 20 to 50 rows in the result. The table will be holding more than100000 rows in it.
I am only worried about the select query. So to improve the performance, I decided to split the table into two tables with id in both the tables as the primary key, with the mostly used 15 attributes in one table and other 85 attributes in the other table. Do you think it is a good way of DB design or should the entire 100 attributes remain in a single table?
2006-12-21 09:28:57 · 7 answers · asked by baadshah 1 in Computers & Internet ➔ Programming & Design
the number of columns in a DB do little to impact performance. It is all about how many rows, and what fields you are most often querying on.
A table with 100 attributes sounds like it may be able to be broken up. Here are a few questions...
Of the 100 attributes, do all rows have a value for all 100 attributes?
When entering rows, do you find youself using values of N/A, Null, or '' (empty string)?
Do you have any multi valued attributes such as phone1, phone2, phone3? Even worse, how about phone1, phonetype1, phone2, phonetype2, etc?
If you ansered yes to any of these qeustions, then breaking this up into multiple tables with the same primary key value is probably a good idea.
2006-12-21 09:52:26 · answer #1 · answered by Doug k 3 · 0⤊ 0⤋
Well, there are better ways to handle this, but definiately separating the tables is the right thing to do, it will help increase the performance of queries. Don't forget to link the tables with a foriegn key. U could use the id column as the foreign key, even if it is the primary key
2006-12-21 11:06:19 · answer #2 · answered by BEN 1 · 0⤊ 0⤋
The post which talked about the number of rows is correct. If you can limit the rows to only what you need, your performance will improve. Also, if you can index one or more of your searchable attributes, your performance should be much better. Good luck!
2006-12-21 16:06:37 · answer #3 · answered by Dave 5 · 0⤊ 0⤋
Sure you can do that, you can always query for the data in the other table/s by joining the key fields. That's really not an uncommon thing to do to improve performace.
2006-12-21 09:37:26 · answer #4 · answered by GirlUdontKnow 5 · 0⤊ 0⤋
Your plan is definitely the way to go.
It's why they are called relational databases; the tables share relationships based on keys.
2006-12-21 13:21:50 · answer #5 · answered by Anonymous · 0⤊ 0⤋
If your database supports views create a view of the full table. You can change what's under the view later if you need to.
2006-12-21 12:02:23 · answer #6 · answered by glenbrent 2 · 0⤊ 0⤋
RENAME login TO new_login
2016-03-29 02:59:04 · answer #7 · answered by Anonymous · 0⤊ 0⤋