How can I optimize this mysql query? Script Posted

Question

So I have this code below that I use to connect and execute commands to the mysql on the server and it is used for an authentication system for any .NET project. Now when I get around 150+ users online it will start to slow down the site and the mysql starts using tons of resources. Is there a way to make these mysql query's execute faster or just not get hung up?

$bn123 = $_GET['bn'];
    $hwid = $_GET['hwid'];
    $ip = getRealIpAddr();
    $pcname = $_GET['pcname'];
    $country123 = "N/A";
    $time123 = time();

    $GetRows = $odb->query("SELECT * FROM `Account` WHERE bn = '$bn123'");
    $row_count = $GetRows->rowCount();
    $count = $row_count;
    if($count == 0){
        $odb->exec("INSERT INTO `logs` (id, bn, cpukey, ip, pcname, time, log) VALUES(NULL,'$bn123','$hwid','$ip','$pcname','$time123','SUCCESS - Bot Registered.')");
        $odb->exec("INSERT INTO `Account` (id, bn, cpukey, banned, ip, country, pcname, note, online, function, lastlogon) VALUES(NULL,'$bn123','$hwid','0','$ip','$country123','$pcname','','0','','')");
    }

    $SQLGetUsers = $odb -> query("SELECT * FROM Account WHERE `bn` = '$bn123'") or die(mysql_error()); 
    while ($getInfo = $SQLGetUsers -> fetch(PDO::FETCH_ASSOC))
    {
        $banned123 = $getInfo['banned'];
        if ($banned123 == 0) {
            $str = "Not Banned";
        } else {
            $str = "Banned";
        }
    }
    $response = $str;
    if ($response == "Not Banned")
    {
        $finaleresponse = "Success";
        $odb->exec("UPDATE Account SET `online` = '1', `lastlogon` = '$time123', `ip` = '$ip', `country` = '$country123', `pcname` = '$pcname' WHERE `bn` = '$bn123'");
    } 
    else if ($response == "Banned")
    {
        $finaleresponse = "Banned";
        $odb->exec("UPDATE Account SET `online` = '0', `lastlogon` = '$time123', `ip` = '$ip' WHERE `bn` = '$bn123'");
        $odb->exec("INSERT INTO logs (id, bn, cpukey, ip, pcname, time, log) VALUES(NULL,'$bn123','$hwid','$ip','$pcname','$time123','ERROR - User banned.')");
    }
    else
    {
        $finaleresponse = "Error";
        $odb->exec("UPDATE Account SET `online` = '0', `lastlogon` = '$time123', `ip` = '$ip' WHERE `username` = '$username1'");
        $odb->exec("INSERT INTO logs (id, username, cpukey, ip, pcname, time, log) VALUES(NULL,'$username1','$hwid','$ip','$pcname','$time123','ERROR - Something went wrong.')");
    }
    echo Encrypt($finaleresponse);

Please let me know any ways to optimize this code as I thought it was already optimized, thanks! Also here is the db.php:

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', '');
define('DB_USERNAME', '');
define('DB_PASSWORD', '');

$odb = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
?>

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jan 31 '17 at 21:56
First, use prepared statements/parameterized queries. Second, use EXPLAIN queries to make sure your queries are using table indexes properly - if there are indexes. If not, you need to INDEX tables to increase speed. — Jay Blanchard, Jan 31 '17 at 22:01
Together with @JayBlanchard advice, try to find which query is slowing down your code. After that use EXPLAIN and, if still with doubt, update your post with a more specific question. — jfneis, Jan 31 '17 at 22:08

score 0 · Answer 1 · answered Feb 01 '17 at 01:05

The INSERTs "can't" be slow. So it must be the 5 SELECTs and UPDATEs. 5 is not a big number, so there must be a missing index.

All 5 need an index on bn on Account? (Please provide SHOW CREATE TABLE for each table involved, so I won't make suggestions that are already done.)

Also,... Don't fetch the same row(s) from Account twice.

How can I optimize this mysql query? Script Posted

1 Answers1