MySQL and PHP optimizing searching

Question

I currently have a buddy system that requires a lot of queries and has a lot of users. My method of searching for a specific user worked for a while until the amount of entries started to get into the 10s of thousands. The way I currently search for a user is like this:

            $results = mysql_query("SELECT * FROM Info");           
            while($row = mysql_fetch_array($results)){
                $ignFromDB = $row['IGN'];
                if($ignFromDB == $ign){
                    $friendsFromDB = $row['Friends'];
                    $frArr = explode(':', $friendsFromDB);
                    return $frArr;
                }
            }

Since I now need to search through 10s of thousands of entries it becomes a very inefficient and slow approach. As a way of solving this I decided to only have one search request per user: At startup they would request their entry index and then would send a request with that index so I wouldn't need to loop. I could just select the entry with that index. Only problem is I have very little knowledge of MySQL and I need to know how to do this. Any help would be greatly appreciated!

score 2 · Answer 1 · edited May 23 '17 at 11:50

2

Wow so much for just a WHERE clause. You are going to market, buying all the apples and then coming back home and then start checking which of the thousands do you really need. Why not simply tell the shopkeeper your requirement and get only the required apple. Imagine the cost! You just need

SELECT * FROM Info WHERE IGN='$ign'

And that's it

Must Read: Warning About SQL Injection

edited May 23 '17 at 11:50

Community

1
1

answered Jul 17 '14 at 04:33

Hanky Panky

44,997
8
67
93

Wouldn't that still loop through all the entries until it found one that matched? – user3328139 Jul 17 '14 at 04:35
Nope it wont. MySQL will not return all the entries. It will only return the ones that have same IGN, so your PHP loop will have almost down to 1 value only if you don't have duplicates. If you do, then only that much but never the whole table. That is exactly what the WHERE clause is for – Hanky Panky Jul 17 '14 at 04:36
Oh, OK. Thank you very much. – user3328139 Jul 17 '14 at 04:48

score 0 · Answer 2 · answered Jul 17 '14 at 04:35

Try this code

           $results = mysql_query("SELECT * FROM Info  WHERE IGN='$ign'");           
            while($row = mysql_fetch_array($results)){                      
                    $friendsFromDB = $row['Friends'];
                    $frArr = explode(':', $friendsFromDB);
                    return $frArr;                    
            }

MySQL and PHP optimizing searching

2 Answers2