Does next_result() affect query performance? One article analysis

When using PHP and MySQL for database operations, many developers may have encountered a problem: when executing multiple SQL statements , mysqli::next_result() is required, and this function is often questioned "will slow down the query speed"? Today we will explore the ins and outs of this issue to help you understand the truth.

1. What is next_result()

When executing multiple SQL statements using mysqli_multi_query() , the results of each statement need to be processed manually. At this time, the function of next_result() is to move the internal pointer to the result set of the next SQL statement:

 $conn = new mysqli("localhost", "user", "password", "database");

$sql = "SELECT * FROM users; SELECT * FROM orders;";

if ($conn->multi_query($sql)) {
    do {
        if ($result = $conn->store_result()) {
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        }
    } while ($conn->next_result());
}

2. Does next_result() really slow down?

1. Essence analysis

next_result() is not essentially executing a query , but tells the MySQLi client that "can get the next result from the server." Its main time consumption comes from:

• Network latency (client-server communication)

• Large data volume (memory will be consumed when the result set is large)

• Non-essential processing (unprocessed results also need to be cleaned)

So, if your query itself is already fast, the call to next_result() is almost negligible. But if the query produces a large amount of data, each move of the result set means that the previous result needs to be parsed and released, which can indeed incur additional overhead.

2. Performance comparison experiment

Suppose we execute the following multi-query statement in the test:

 $sql = "
  SELECT * FROM big_table_1;
  SELECT * FROM big_table_2;
  SELECT * FROM big_table_3;
";

The data volume for each table is around 50,000 rows.

We perform two sets of tests:

• A: Use multi_query() + next_result() to parse each result normally;

• B: Use multi_query() but only the first result is taken, and the rest is ignored;

The statistics are as follows (in seconds):

As you can see, if you don't need the subsequent result set, it will be faster if you really don't call next_result() . But as long as you need to get this data, it is indispensable.

3. Correct usage and optimization suggestions

1. Don't use it if you don't need it

If you only need the query result of one statement, try to avoid using multi_query() :

 // More recommended way
$result = $conn->query("SELECT * FROM users");

2. Reduce the result set size

When using multi_query() , control the amount of data returned by each query statement as much as possible. For example, select only the necessary fields, or use LIMIT to limit the number:

 SELECT id, name FROM users LIMIT 100;

3. Pay attention to releasing resources

Be sure to call $result->free() after store_result() to prevent memory accumulation, especially when used in loops.

4. Conclusion

• next_result() itself does not "slow down the query", it is only part of the process;

• What really affects speed is the amount of data and whether you really need all the results;

• If you only need one query, please avoid multi_query() ;

• If you need multiple results processing, next_result() is your good helper.

For more in-depth performance analysis, please visit our detailed comparison report at https://gitbox.net/articles/db-performance .