PHP PDO mysql multiple queries mak

Question

I ran into a problem, I'm using MyIsam tables in mysql database, using PDO to connect.

In all my tests (calling for 1 user everything works fine), now having around 300 users connected at the same time all day making requests is really really slowing down mySQL. Ive got my.cnf with (key_buffer=512M and max_connections=200) my server is VPS with 2GB RAM and 3 cores. And each table has around 40K records

All queries/updates/inserts are super slow, but I've discovered that this PHP file is the root of the problem, it's requested every 10 seconds for every user (not at the same time, and if the call fails it's delayed another 10 seconds):

 //User Info ----------------------- 
    $sqlcommand = "SELECT * FROM tbl_User WHERE FB_ID='". $FB_ID . "'";
    $statement = $pdo->query($sqlcommand);
    $row = $statement->fetch(PDO::FETCH_ASSOC); 

    if($row) {
       //Do its thing
    }
//How Many Gifts are there----------------- 
    $sqlcommand = "SELECT * FROM tbl_Gifts_Sent WHERE FB_ID='". $FB_ID . "'";

    $statement = $pdo->query($sqlcommand);
    $row = $statement->fetchAll();

    if (!$row) {
        $amountOfGifts=0;
    } else {
        foreach ($pdo->query($sqlcommand) as $row){
                $amountOfGifts=$amountOfGifts+1;
        }
    }

//Daily Leaderboard King--------------- 
    $sqlcommand = "SELECT * FROM tbl_Leaderboard_DAY WHERE Today = " . $midnight . " ORDER BY XP_Gain DESC";

    $statement = $pdo->query($sqlcommand);
    $row = $statement->fetch(PDO::FETCH_ASSOC); 
    if ($row) {
        //Do its thing
    } 

//-------- THE STATE ON ALL Themes WITH GIFTS AND ALL    
    $sqlcommand = "SELECT * FROM tbl_User_Reel WHERE FB_ID='". $FB_ID . "'";
    foreach ($pdo->query($sqlcommand) as $db_gmInfo){  
         //DO its thing
    }

this 4 queries are on the same php file.

Im a newbie in this and this is my first php mysql project, so I'm thinking there's gotta be a better way to do this, :S any recommendations, help or pointers on how to make it work super fast are greatly appreciated! thank you

Answer 1

One thing I can see right off the bat is that you're likely to be selecting more data than you need. A good rule of thumb is to never use SELECT * FROM. Only select the columns that you need for your query.

For example, in your gifts query you could change the query to "SELECT COUNT(*) FROM tbl_Gifts_Sent WHERE FB_ID='". $FB_ID . "'"; and only ever get one row with one field to check. You could then use $row = $statement->fetch(PDO::FETCH_NUM); and examine $row[0] to find out whether there are any rows that match that $FB_ID and use that value for $amountOfGifts. That will save you from loading too much data (all the fields in tbl_Gifts_Sent as well as saving you from looping over a potentially large resultset in PHP (much slower than letting MySQL do the work). Revised full (untested) code below:

//How Many Gifts are there----------------- 
    $sqlcommand = "SELECT COUNT(*) FROM tbl_Gifts_Sent WHERE FB_ID='". $FB_ID . "'";

    $statement = $pdo->query($sqlcommand);
    $row = $statement->fetch();

    if (!$row) {
        $amountOfGifts=0;
    } else {
        $amountOfGifts = $row[0];
    }

Also, I don't know where you're getting your query variables like $FB_ID, but it's probably a bad idea to trust them not to be SQL Injection vectors. You should probably be using PDO::prepare to build your statements; that will at least minimise the injection risk.

Answer 2

Followed Cori's suggestions to prevent SQL Injections but what really sped up mySQL was that the column FB_ID was not indexed, after I modified my code to not search for FB_ID but rather the ID (primary,indexed,autoincrement) mysql results suddenly became MUCH faster.