Current Location: Home> Latest Articles> When processing big data, how should the mysqli_result::$lengths function be used to be the most efficient?

When processing big data, how should the mysqli_result::$lengths function be used to be the most efficient?

gitbox 2025-05-26

Performance and efficiency are key considerations when using PHP to operate MySQL databases, especially when processing large-scale query results. mysqli_result::$lengths is an attribute in the MySQLi extension that gets the data length of each field in the current result set. Making it rationally helps optimize data reading and processing processes and improve program execution efficiency.

mysqli_result::$lengths Introduction

$lengths is an attribute of the mysqli_result object, which returns an array, and each element in the array corresponds to the byte length of the corresponding field in the current result row. Its typical usage scenario is to use it in conjunction with methods such as mysqli_stmt::fetch or mysqli_result::fetch_row, etc. , which can accurately obtain the field data size and avoid the additional memory overhead caused by unclear field content length.

Why use $lengths

Knowing the exact length of the data can help you when processing fields containing large text, large binary data (BLOB), or irregular length:

  • Avoid unnecessary string truncation or extension.

  • Optimize memory management to avoid reserveing ​​too large or too small cache space.

  • Streaming read data provides accurate boundaries and reduces duplicate reads.

How to use $lengths efficiently

The following example demonstrates how to combine $lengths to read and process data when processing big data to ensure efficiency:

 <?php
$mysqli = new mysqli("gitbox.net", "user", "password", "database");

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

$query = "SELECT id, large_text_column FROM big_table";
$result = $mysqli->query($query);

if ($result === false) {
    die("Query failed: " . $mysqli->error);
}

// Iterate over the result set,use lengths Get the byte length of each field
while ($row = $result->fetch_row()) {
    // $result->lengths is the length array of all fields in the current row
    $lengths = $result->lengths;

    // For example, get large_text_column Field length(Assume it is the second field)
    $textLength = $lengths[1];

    // Control the processing of fields through length
    $largeText = substr($row[1], 0, $textLength);

    // It&#39;s OK here $largeText Make further processing,For example, writing to a file,Or streaming
    echo "ID: " . $row[0] . " - Text length: " . $textLength . "\n";
}

$result->free();
$mysqli->close();
?>

illustrate

  • Get the byte length of each field of the current line through $result->lengths , avoiding the additional overhead of string length calculation.

  • For large text fields, directly using $lengths can accurately read the data length to avoid inaccurate length judgment due to character encoding differences.

  • Combining functions such as substr , we can do more fine memory management and data slicing.

Additional optimization suggestions

  • Use unbuffered queries : For particularly large result sets, using unbuffered queries ( MYSQLI_USE_RESULT ) can reduce memory usage and work better with $lengths .

  • Avoid unnecessary data transmission : only query the required fields to reduce transmission pressure.

  • Streaming large fields : When processing BLOBs or large text, consider streaming reading rather than one-time loading.

Summarize

mysqli_result::$lengths is a detailed but extremely useful property when PHP operates MySQL. Especially when dealing with large data query results, it can accurately control data length and memory allocation and improve program performance. It can better play its advantages with unbuffered queries and streaming processing.