mysqli_result::fetch_column is used for memory optimization when extracting single column data

When processing database query results in PHP, if we are only interested in a column in the result set, using mysqli_result::fetch_column() is a very efficient way. This approach is more memory-saving than traditional fetch_assoc() or fetch_row() , especially performs better when dealing with large amounts of data. This article will explore how to further optimize memory usage during the use of fetch_column() to improve the overall performance of PHP programs.

1. Limit the number of columns in the query result

One of the most direct and efficient ways to optimize memory is to select only the column you actually need in SQL queries instead of using SELECT * . For example:

 $result = $mysqli->query("SELECT username FROM users");

This can reduce the amount of data transmitted by the database server to PHP and reduce memory consumption from the source.

2. Use fetch_column() instead of fetch_assoc() or fetch_row()

fetch_assoc() returns an associative array, fetch_row() returns an index array, and fetch_column() directly returns a single value:

 while ($username = $result->fetch_column()) {
    // deal with$username
}

This method avoids unnecessary array encapsulation and reduces memory allocation, especially for scenarios where only a certain column of data is required.

3. Avoid cache all data into arrays

A common practice is to cache all results into an array first and then process:

 $usernames = [];
while ($username = $result->fetch_column()) {
    $usernames[] = $username;
}

If the data volume is large, this can lead to a large amount of memory usage. If the processing logic allows it, try to read and process it while reading, rather than loading all data at once:

 while ($username = $result->fetch_column()) {
    process_username($username); // 假设是某个deal with函数
}

4. Set up a reasonable mysqli buffering strategy

By default, mysqli_query() caches all results in client memory. If the data volume is particularly large, you can use the MYSQLI_USE_RESULT constant to enable non-buffered queries:

 $result = $mysqli->query("SELECT username FROM users", MYSQLI_USE_RESULT);

This method will not load all the results into memory, but will obtain one line of data from the server at a time, saving more memory. However, it is necessary to note that non-buffered result sets cannot execute other queries before processing.

5. Configure PHP memory limit rationally

If your PHP script itself performs high-load tasks (such as large amounts of data extraction), make sure that the memory_limit in your php.ini is set reasonably, to prevent memory overflow, and not to set too high and waste system resources. It can be dynamically set by the following methods:

 ini_set('memory_limit', '128M');

However, it is better to optimize program logic rather than simply increasing memory caps.

6. Use the Generator to further reduce memory usage

For functions that need to return multiple column values, the generator can be combined with the effect of delayed loading:

 function get_usernames(mysqli_result $result): Generator {
    while ($username = $result->fetch_column()) {
        yield $username;
    }
}

// use
foreach (get_usernames($result) as $username) {
    process_username($username);
}

The advantage of the generator is that it does not load all data into memory at once, which is particularly suitable for handling large result sets.

summary

Using mysqli_result::fetch_column() itself is a memory optimization practice, but combined with other techniques such as querying only necessary fields, extracting data while processing, and using non-buffered queries and generators, it can further reduce the memory consumption of PHP programs and improve performance. These optimization methods are particularly important for processing scenarios with large data volumes.

More practical experiences about high-performance PHP interacting with databases can be found at: https://gitbox.net/php-db-optimization