Searching the database, designing for speed?
Posted: Mon Aug 08, 2005 8:35 pm
Hi all,
I'm wanting to make sure my design is solid, so I thought I'd ask for feedback here. It particularly pertains to making it fast to search.
Let's assume I have lots of tables in my MySQL database, they could be like this:
I then have a single table that contains user info, the user has filled in what their car has, and the user table is similar to this
The idea is, that the user has chosen his/her options on signup. The options were created from the earlier tables shown, so everybody's responces (and hence profile) is taken from, and can be searched from the earlier tables.
So far so good (I hope you're still with me
).
The actual question is:
(Q)-Should the 'user_table' contain the actual data from within the other tables, or should the 'user_table' contain links to the id's containing the data in the other tables?
The reason for this question is speed. Let's assume the site is VERY popular and I have say, 10,000 people all doing a search at the same time.
Method 1
If I have the actual DATA in the user_table, the search is as simple as "show me all users vehicles with 4 tyres, a big stereo and that have 6 cylinders'. The search should be fast as it only searches the one table, table 'user_data'.
Method 2
If I have links to the DATA in the OTHER tables, then it become more akin to: Search user_table, check user_table and compare user_tyres against car_tyres, compare user_stereo against car_stereo and compare user_cylinders against car_cylinders.
I'm guessing method 2 would be MUCH more intensive? Is this so? Is the hit on processing power etc dramatically different?
I think I'd rather run on method 2, but I'd like feedback, and I really appreciate anything anyone has to say as it's going to shape my whole site and I'm just about ready to move into this aspect of the site.
I look forward to some good discussion
Rob
I'm wanting to make sure my design is solid, so I thought I'd ask for feedback here. It particularly pertains to making it fast to search.
Let's assume I have lots of tables in my MySQL database, they could be like this:
Code: Select all
car_cc
car_tyres
car_cylinders
car_stereo
car_speakersCode: Select all
user_table
-username
-user_age
-user_gender
-user_cc
-user_tyres
-user_cylinders
-user_stereo
-user_speakersSo far so good (I hope you're still with me
The actual question is:
(Q)-Should the 'user_table' contain the actual data from within the other tables, or should the 'user_table' contain links to the id's containing the data in the other tables?
The reason for this question is speed. Let's assume the site is VERY popular and I have say, 10,000 people all doing a search at the same time.
Method 1
If I have the actual DATA in the user_table, the search is as simple as "show me all users vehicles with 4 tyres, a big stereo and that have 6 cylinders'. The search should be fast as it only searches the one table, table 'user_data'.
Method 2
If I have links to the DATA in the OTHER tables, then it become more akin to: Search user_table, check user_table and compare user_tyres against car_tyres, compare user_stereo against car_stereo and compare user_cylinders against car_cylinders.
I'm guessing method 2 would be MUCH more intensive? Is this so? Is the hit on processing power etc dramatically different?
I think I'd rather run on method 2, but I'd like feedback, and I really appreciate anything anyone has to say as it's going to shape my whole site and I'm just about ready to move into this aspect of the site.
I look forward to some good discussion
Rob