...

View Full Version : how to reduce mysql queries



gani
05-23-2011, 02:45 PM
database A = members names
Database B = Friends of members

When a user logs into mysql, the server finds all records of that user's friends in database B. Then for each record, it has to make a query in database A to get the name belonging to the record.

Problem is, if user has 1000 friends, I need to make 1000 requests to database getting all the names.

Is there any way to do this with just one request somehow :confused::(

sunfighter
05-23-2011, 05:38 PM
Why not just add the name to DB2? Actually, add everything that is needed to DB2.

Keleth
05-23-2011, 07:05 PM
Um... I think this could easily be done in 1 query. I will assume a general structure (and I assume you mean table A and B... not databases... if you do mean databases, you'll need to provide structures to see what can be done):

Table_A:
userID int
name varchar(50)

Table_B:
userID int
friendID int


SELECT friendNames.userID, friendNames.name
FROM Table_A curUser, Table_B b, Table_A friendNames
WHERE curUser.userID = (user's ID) AND b.userID = curUser.userID AND b.friendID = friendNames.userID

gani
05-23-2011, 07:06 PM
ahhh .... how exactly do you propose i do that ?

each member can have a 1000 friends .... and each friend can be a friend of 1000 members.... :(

gani
05-23-2011, 07:19 PM
hi keleth ...
we posted at the same time... thank you for your solution... but do you have any idea how efficient this type of query is? I mean ... if both tables have millions of records, do you think if i do a query like this that it willl be fast ?

Keleth
05-23-2011, 07:31 PM
While I can't speak to millions, I have used similar queries in tables with a few hundred thousand, but I don't see why it should be slow. Its basically just a 3 table join on a select... As long as your tables are setup properly with keys, it should be fine. You're only putting one record from table A, and a few hundred/thousand from table B... I don't see any issues.

With due respect to sunfighter though, I wouldn't put all the data into table B though... you're repeating data that doesn't need to be repeated, it makes the design inefficient and can lead to data artifacts.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum