Question

0

165

Views

Speed up (uncachable) MySQL queries

I have some PHP session test code - to ensure the logged in user is valid. I'll just explain some of the functions: is_logged_in just checks to see if some session variables are set and test_duplicate connects to the database to check if there is a row with a column equaling the value.

function check_account(){
  if(is_logged_in()){
    // Destroy session if the user doesn't exist.
    if(test_duplicate("username", $_SESSION["user"])){
      session_destroy();
    }
  }

  if($_SESSION["valid_day"] != date("Ymd")){
    // Destroy session if the key has expired
    session_destroy();
  }
}



function test_duplicate($field, $value){
  $mysqli = database_connect();
  $statement = $mysqli->prepare("SELECT * FROM users WHERE " . $field . " = ?");
  $statement->bind_param("s", $value);
  $statement->execute();
  $statement->store_result();

  $rows = $statement->num_rows;
  if($rows > 0){
    return FALSE;
  }

  return TRUE;
}

function database_connect(){
  if(!isset($dbconnection)){
    $config = get_configuration();

    // Create connection
    $mysqli = new mysqli($config["hostname"], $config["username"], $config["password"], $config["database"]);

    if($mysqli->connect_error){
      die("<h1>Error 1 :: Critical backend failure</h1>");
    }

    if(!check_tables($mysqli)){
      die("<h1>Error 2 :: Critical backend failure</h1>");
    }

    $dbconnection = $mysqli;

    return $mysqli;
  }else{
    return $dbconnection;
  }
}

The problems:

This test_duplicate function takes about 3-4 seconds to complete
Caching won't work because I need to test that value (it's determining whether the user still exists in the database)

My current solutions (I don't think these are particularly good):

Only running the code randomly (i.e. a 1/5 chance)
Not running the code (but then I'm not sure how to implement this alternatively)

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-05-26T17:02:02.258Z

I would suggest:

Add debug code to measure how much time it takes for each line of code in test_duplicate function.
Caching the connection and share it amongst your components like @jeroen suggests in the comments.
Instead of selecting all fields with select *, return a single integer value. Better still instead of hadling it as a row returning query, hadle it as a scalar query that returns a single value.

Update

Here's a query that returns 1 if there is a row with a specified value in username; or 0 if no matching row exists:

select
  ifnull(
    (
      select
        1 as user_exists
      from
        dual
      where
        exists(
          select * from users where username = 'test2'
        )
    ), 0) as result
from dual

0

165

Speed up (uncachable) MySQL queries

1 answers

0

Find remote jobs

Andres GPT