English
English
When working with MySQL databases in PHP, you often need to extract data from query results. mysqli_result::fetch_assoc and mysqli_fetch_row are two commonly used methods for fetching rows from a result set. While both return query results, they differ in performance, return formats, and applicable scenarios. In this article, we will explore their performance differences, analyze their respective features, and discuss which method is more suitable for large-scale queries.
mysqli_result::fetch_assoc:
The fetch_assoc method returns an associative array where the keys are column names and the values are the corresponding column values. This approach is generally easier to understand and use, as it allows direct access to result data by column name.
Example:
<span><span><span class="hljs-variable">$result</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">query</span></span><span>(</span><span><span class="hljs-string">"SELECT id, name FROM users"</span></span><span>);
</span><span><span class="hljs-keyword">while</span></span><span> (</span><span><span class="hljs-variable">$row</span></span><span> = </span><span><span class="hljs-variable">$result</span></span><span>-></span><span><span class="hljs-title function_ invoke__">fetch_assoc</span></span><span>()) {
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"ID: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span><span>[</span><span><span class="hljs-string">'id'</span></span><span>] . </span><span><span class="hljs-string">" - Name: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span>[</span><span><span class="hljs-string">'name'</span></span><span>] . </span><span><span class="hljs-string">"<br>"</span></span><span>;
}
</span></span>mysqli_fetch_row:
The fetch_row method returns a numerically indexed array, where the column values are arranged in the order they appear in the query result. This method is slightly more efficient than fetch_assoc because it doesn’t need to create key-value mappings with column names.
Example:
<span><span><span class="hljs-variable">$result</span></span><span> = </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">query</span></span><span>(</span><span><span class="hljs-string">"SELECT id, name FROM users"</span></span><span>);
</span><span><span class="hljs-keyword">while</span></span><span> (</span><span><span class="hljs-variable">$row</span></span><span> = </span><span><span class="hljs-variable">$result</span></span><span>-></span><span><span class="hljs-title function_ invoke__">fetch_row</span></span><span>()) {
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"ID: "</span></span><span> . </span><span><span class="hljs-variable">$row</span></span>[</span><span><span class="hljs-number">0</span></span>] . </span><span><span class="hljs-string">" - Name: "</span></span> . </span><span><span class="hljs-variable">$row</span></span>[</span><span><span class="hljs-number">1</span></span>] . </span><span><span class="hljs-string">"<br>"</span></span>;
}
</span></span>In terms of performance, mysqli_fetch_row typically uses less memory than mysqli_result::fetch_assoc. This is because fetch_row returns a simple numeric array, whereas fetch_assoc returns an associative array, which requires additional memory to store the mapping between column names and values.
Specifically, fetch_assoc includes column name strings in each returned array, while fetch_row only returns numeric indices. This means that fetch_row is more memory-efficient, especially when the result set contains a large number of columns, where the difference becomes more noticeable.
In terms of execution speed, mysqli_fetch_row also tends to be slightly faster than mysqli_result::fetch_assoc. This is because fetch_assoc requires extra processing time to map column names to array keys, whereas fetch_row simply returns column values in sequence without additional mapping, giving it a slight speed advantage.
Of course, this performance difference may be negligible for small datasets, but when dealing with very large amounts of data, fetch_row’s advantage becomes more apparent, especially when you need to handle large volumes of data repeatedly.
Ease of understanding and maintenance: fetch_assoc returns an associative array, making data access more intuitive. Using column names as array keys is especially convenient when working with complex query results.
When column names are needed: If the query returns many columns and you need to access them by name rather than index, fetch_assoc is more suitable. This is particularly helpful when working with large tables containing many columns.
High performance requirements: Because it uses less memory and is faster, mysqli_fetch_row is ideal for scenarios involving very large datasets. When processing millions of rows, it can significantly reduce memory usage and improve processing speed.
Fixed column order: If you already know the column order of your query results and do not need to use column names, fetch_row is simpler and more efficient.
When handling large-scale queries, performance becomes a critical factor. Since mysqli_fetch_row has advantages in both memory usage and execution speed, it is generally the better choice for large data queries, particularly when you only need to access data in sequence.
However, if the query involves many columns and you frequently need to access data by column name, mysqli_result::fetch_assoc—while slightly slower—still offers valuable code readability and maintainability benefits.
Performance comparison: mysqli_fetch_row is usually more efficient than mysqli_result::fetch_assoc, especially with large datasets.
Applicable scenarios: mysqli_result::fetch_assoc is better suited for scenarios where accessing data by column name is common, while mysqli_fetch_row is more suitable for optimizing memory usage and performance in large-scale queries.
Recommendation: If performance is the top priority and you don’t need to access data by column name, use mysqli_fetch_row. If readability and development convenience matter more and the dataset is relatively small, mysqli_result::fetch_assoc is the better choice.
Related Tags:
mysqli_result