View Full Version : how to reduce mysql queries

05-23-2011, 03: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::(

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

05-23-2011, 08: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):

userID int
name varchar(50)

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

05-23-2011, 08: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.... :(

05-23-2011, 08: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 ?

05-23-2011, 08: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.