How to calculate the execution time of a SQL query using the gettimeofday function?

1. Why choose gettimeofday?

• : The return time is accurate to microseconds, suitable for short-term measurements.

• Ease of use : No additional extensions are relied on, PHP native support.

• Good compatibility : available on most Unix-like systems and PHP environments.

2. Introduction to gettimeofday function

The syntax of the gettimeofday function is as follows:

 array gettimeofday([bool $return_float = false])

• If $return_float is set to true , the float (seconds. microseconds) of the current time is returned.

• If false is the default value, an array containing the number of seconds and microseconds is returned.

For example:

 $time = gettimeofday(true);
echo $time; // For example output：1685021654.123456

3. Calculate execution time in combination with SQL query

Here is a typical example of using MySQLi to perform SQL queries and calculate time:

 <?php
// Connect to the database
$mysqli = new mysqli("gitbox.net", "username", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Record query start time
$start = gettimeofday(true);

// implement SQL Query
$sql = "SELECT * FROM users WHERE status = 1";
$result = $mysqli->query($sql);

if (!$result) {
    die("Query错误: " . $mysqli->error);
}

// 记录Query结束时间
$end = gettimeofday(true);

// 计算implement时间
$duration = $end - $start;

echo "SQL Queryimplement时间： " . round($duration, 6) . " Second\n";

// Release the result set
$result->free();

// Close the connection
$mysqli->close();
?>

Code description:

• Use gettimeofday(true) to get the number of floating point seconds with a decimal point.

• Record $start before the query starts, and record $end after the query.

• Subtraction between the two is the number of seconds used for the query (floating point number, including microseconds).

• Use round() to retain 6 decimal places to display microsecond time.

4. Summary

Using the gettimeofday function to accurately calculate the execution time of SQL query can help developers discover performance bottlenecks and further optimize database query efficiency. This method is simple and efficient, suitable for most PHP application scenarios.