Optimize MySqli request

Question

I'm building a website that works like a console. I have a status script that check if the page is offline or you're blacklisted/whitelisted. I do this by look up the IP in my database. Then after that I use a switch to direct the user. I use javaScript to redirect because the site uses ajax and I can't get header('Location: xxx'); to work with it(A problem for another time).

When I run the site with the status page it loads twice as slow, and it doesn't help that page loads this script each time the user uses a command.

Do you have any suggestions on how to optimize it to load faster? I´m open for all ideas. Thanks for your time.

 if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    } else {
        $ip = $_SERVER['REMOTE_ADDR'];
};

$conn = new MySQLi('localhost', 'xxx', 'xxx', 'xxx');

$statusRank = "";

$sql = "SELECT enable FROM global WHERE enable = '1' AND setting = 'frontend'";
$query  = $conn->query($sql) or die ($conn->error);
if(mysqli_num_rows($query)) {
    $statusRank = "offline";
}

$sql = "SELECT ip FROM blacklist WHERE ip = '$ip'";
$query  = $conn->query($sql) or die ($conn->error);
if(mysqli_num_rows($query)) {
    $statusRank = "blacklist";
}

$sql = "SELECT ip FROM whitelist WHERE ip = '$ip'";
$query  = $conn->query($sql) or die ($conn->error);
if(mysqli_num_rows($query)) {
    $statusRank = "whitelist";
}

switch ($statusRank) {
    case "blacklist":
        ?><script>window.location.replace("include/blacklist.php");</script><?php
    break;
    case "whitelist":
    break;
    case "offline":
        ?><script>window.location.replace("include/offline.php");</script><?php
    break;
    default:
};

which one is the query that loads slow? "Twice as slow" is no value you can work with.. is it a millisecond? Minute? Hour? And without your table structure / indexes there is no chance to help you. — Olli, Dec 21 '15 at 15:03
Sorry... I goes from being as close to instant as it gets maybe around 300-500 millisecond and when the script is enabled +/- 2 sec. — Nygaard, Dec 21 '15 at 15:12

score 1 · Accepted Answer · edited May 23 '17 at 12:30

Here are a few ideas starting with the easiest to implement :

Make sure you have an index on the ip column in the blacklist and whitelist tables.
I am not very familiar with this in PHP but you should probably use a persistent connection. Opening a database connection is an expensive operation. See php connection pooling mysql for more details.
Finally you could merge the three queries together and instead of counting the rows just check if the ip is found or not. Something like :

SELECT
  CASE WHEN EXISTS 
    (SELECT * FROM global WHERE enable = '1' AND setting = 'frontend') THEN 'T' ELSE  'F' 
  END AS offline,
  CASE WHEN EXISTS 
    (SELECT * FROM blacklist WHERE ip = '$ip') THEN 'T' ELSE 'F'
  END AS blacklisted,
  CASE WHEN EXISTS
    (SELECT * FROM whitelist WHERE ip = '$ip') THEN 'T' ELSE 'F'
  END AS whilelisted

score 1 · Answer 2 · answered Dec 21 '15 at 15:55

Read the DB once for the IP, start a PHP session, and save the results to it. So on the 2nd and subsequent loads you read from the session.

I would make your select as:

SELECT 'blacklisted' FROM blacklist WHERE ip = '$ip' JOIN
SELECT 'whitelisted' FROM whitelist WHERE ip = '$ip'

Then you simply have to check each row for strings 'blacklisted' or 'whitelisted' (or b/w for shorter).

Optimize MySqli request

2 Answers2