Looping

[Kryptix]

Never done this before so bare with me. Basically, right now (for example) we have 3 tables... `rscd_friends`, `rscd_ignores` and `rscd_quests`. Each table has it's own query and then loops through the results:

 

result = Query("SELECT `friend` FROM `rscd_friends` WHERE `user` = " + User());

while(result) {

addFriend(friend);

}

 

result = Query("SELECT `ignore` FROM `rscd_ignores` WHERE `user` = " + User());

while(result) {

addIgnore(ignore);

}

 

result = Query("SELECT `quest_id`, `quest_stage` FROM `rscd_quests` WHERE `user` = " +  User());

while(result) {

addQuest(quest_id, quest_stage);

}

 

Now is it possible to basically do all of that in one query and return results like:

 

friend | ignore | quest_id | quest_stage

Bob      Lucy   1          7

John    NULL    2          6

Amy      NULL    NULL      NULL

Paul    NULL    NULL      NULL

Tony    NULL    NULL      NULL

 

I would then need just one loop and a check to see if the column is NULL before doing anything. Something like:

 

while (result) {

if (friend != NULL) { addFriend(friend); }

if (ignore != NULL) { addIgnore(ignore); }

if (quest_id && quest_stage != NULL) ( addQuest(quest_id, quest_stage); }

}

 

My questions:

• Is it possible to do?
  
• Is it worth doing it or should I leave 3 different queries and 3 different loops?
  
• Is it more effient and/or quicker to process?

 

There will generally be 0-100 FRIENDS, 0-10 IGNORES and 0-5 QUESTS so there could be a lot of NULLs but we're having some issues with the player loading time. Currently we're using like 10 queries per-login and when 200 people are all spam-clicking trying to login when we put the game up it uses a lot of queries and drags the CPU down.

 

If it is possible, how would the query go? I tried a few queries but I couldn't g et it to work as each column has to have it's own check. For example, `rscd_friend`.`user` MUST equal User(), `rscd_ignore`.`user` MUST equal User() and `rscd_quests`.`user` MUST equal User().

 

The same is used with 2 other queries that I hope to turn into one as well so any advice would be much appreciated.

[Mchl]

It is possible, but I doubt it would be quicker at all. You would have to do outer join (which in MySQL is emulated by a pair of LEFT JOINs). That would mean you would be actually running 6 (if I am counting correctly) queries instead of three.

[Kryptix]

It is possible, but I doubt it would be quicker at all. You would have to do outer join (which in MySQL is emulated by a pair of LEFT JOINs). That would mean you would be actually running 6 (if I am counting correctly) queries instead of three.

Thanks for your help Mchl. I'll trust your experience and leave it how it is.