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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Well I do a select, insert in mysql with php like this:

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

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

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

php mysql

asked by anonymous 02.03.2017 / 15:37

2 answers

Error loading file for threaded list Two readers on arduino

score 1 · Answer 1

I think it looks 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 did not test, it will not return the exact execution time of the sql, but the total time of sending the request until the return.

score 0 · Answer 2

You can use max_statement_time to prevent the query from taking more than 3 seconds to execute, 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 every SELECT you make in the same session.

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

If your intent is to only identify possible slow and slow querys you can set a slow_query_log_file and then enable slow_query_log , so you can discover the problems in the queries.

If you really want to find the query execution time 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;

Result:

0.00009929
4

Therefore, it would be enough to use:

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