About "mysql result" resources

Question

when I use mysql_query, does Mysql actually perform the query right away, or does it wait until I call mysql_fetch_*() ?

I mean if a the query is very very slow (for eg. the result has many records), which of these functions will run slow? :)

Answer 1

From PHP.net manual of mysql_unbuffered_query,

mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don't have to wait until the complete SQL query has been performed. To use mysql_unbuffered_query() while multiple database connections are open, you must specify the optional parameter link_identifier to identify which connection you want to use.

So for a query that has large mysql result mysql_query will run slow. You should use mysql_unbuffered_query for this.

Lets see whats inside mysql_query in mysql extension.

mysql_query executes the query. If it has any rows to return (SELECT, SHOW etc statements) it fetches the result and stores it in MYSQL_RES C structure. Whether it fetches the result or not depends on whether it calls mysql_use_result or mysql_store_result C function. The later stores the entire result. From mysql_use_result MySQL C API manual,

mysql_use_result() initiates a result set retrieval but does not actually read the result set into the client like mysql_store_result() does. Instead, each row must be retrieved individually by making calls to mysql_fetch_row(). This reads the result of a query directly from the server without storing it in a temporary table or local buffer, which is somewhat faster and uses much less memory than mysql_store_result().

So mysql_store_result reads entire result But mysql_use_result dont.

Now the C code corresponding to mysql_query is

php_mysql_do_query(INTERNAL_FUNCTION_PARAM_PASSTHRU, MYSQL_STORE_RESULT);

Here php_mysql_do_query calls php_mysql_do_query_general and in php_mysql_do_query_general function PHP calls mysql_store_result

    if(use_store == MYSQL_USE_RESULT) { //use_store = MYSQL_STORE_RESULT here
    mysql_result=mysql_use_result(mysql->conn);
} else {
    mysql_result=mysql_store_result(mysql->conn);
}

So mysql_query runs the query right away and store the entire result set in memory. So for large result set it might be slow.

Once mysql_fetch_* functions are called rows are starting to be fetched from PHP's inernal buffer.

Answer 2

Result is fetched when you call mysql_query. mysql_fetch_* just returns the relevant part of the result object.

Answer 3

to answer your question, mysql_query() runs the query, and returns a resource that is used by mysql_fetch_*(). so mysql_query() will be the one that will run slow for a slow query.

Answer 4

As it was said, mysql_query runs the query. So, if the query itself is slow (involves complex or non-optimized conditions, etc), then mysql_query call will take some time.

If the query is fast, but results in too many records, then something like mysql_fetch_all may take time as all the records will have to be copied into PHP memory.