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!

Community · Accepted Answer · 2017-05-23 11:50:19Z

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, 2017 at 11:50

CommunityBot

11 silver badge

answered Jul 17, 2014 at 4:33

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3328139 Over a year ago

Wouldn't that still loop through all the entries until it found one that matched?

Hanky Panky Over a year ago

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

rack_nilesh · Accepted Answer · 2014-07-17 04:35:03Z

0

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;                    
            }

answered Jul 17, 2014 at 4:35

rack_nilesh

5535 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL and PHP optimizing searching

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related