CODEWITHSUNDEEP
phpmysql
Nygaard
Nygaard

Reputation: 75

Optimize MySqli request

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

Upvotes: 0

Views: 354

Answers (2)

Eduardo
Eduardo

Reputation: 7841

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).

Upvotes: 1

ForguesR
ForguesR

Reputation: 3618

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

Upvotes: 1

Related Questions