Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Nov 2008
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to reduce mysql queries

    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

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,712
    Thanks
    25
    Thanked 660 Times in 659 Posts
    Why not just add the name to DB2? Actually, add everything that is needed to DB2.

  • #3
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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

    Code:
    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

  • #4
    Regular Coder
    Join Date
    Nov 2008
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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....

  • #5
    Regular Coder
    Join Date
    Nov 2008
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ?

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •