how to check the time of a select in mysql with php

Question

Well I do a select, Insert in mysql with php that way:

$consulta = $mysqli->query("
    SELECT 
        id, 
        nome
    FROM
       produtos
");

 // Verifica erro
 if (!$consulta) { echo "ocorreu um erro";}

How can I check if select took more than 3 seconds to load?

Answer 1

I believe it’s more or less like this:

$timeInicial = time();

$consulta = $mysqli->query("
SELECT 
    id, 
    nome
FROM
   produtos
");

$timeFinal = time();

$intervalo = $timeInicial->diff($timeFinal);
echo $intervalo->format("%i:%s");

I have not tested, will not return you the exact time of sql execution, but the total time of sending the request until return.

Answer 2

You can use the max_statement_time to prevent the query from taking longer than 3 seconds to run, for example:

Mariadb 10.1:

SET STATEMENT MAX_STATEMENT_TIME = 3000 FOR SELECT * FROM tabela WHERE vaiDemorar = 1

Mysql 5.7.7+: _{(via Optimize Hint)}

SELECT /*+ MAX_EXECUTION_TIME(3000) */ * FROM tabela WHERE vaiDemorar = 1

All cases:

SET SESSION MAX_EXECUTION_TIME = 3000;
SELECT * FROM tabela WHERE vaiDemorar = 1;

This affects all Select you make in the same session.

This will stop the execution of the query by dropping to "an error occurred" if it takes longer than 3 seconds to complete.

---

If your aim is just identify possible slowdowns and slow querys you can define a slow_query_log_file and then enable the slow_query_log, so you can discover the problems in the queries.

---

If you really want to find out the execution time of the query in PHP, then use profiling, as follows:

// Habilite o profiling
$mysqli->query('SET PROFILING = 1');

// Sua query, neste caso um exemplo de 2 + 2:
$matematica = $mysqli->query('SELECT 2 + 2');

list($resultado) = $matematica->fetch_row();

// Obtêm duração da query:
$profiling = $mysqli->query('SHOW profiles');

list(,$tempo_execução) = $profiling->fetch_row();

// Mostra o resultado:    
echo $tempo_execução;
echo '<br>';
echo $resultado;

Upshot:

0.00009929
4

Therefore, it would be enough to use:

if($tempo_execução > 3){
   echo 'Demorou mais de três segundos';
}