[RESOLVED] Difference bewteen using mysql_fetch_row & mysql_stmt method

simongcc

Hello!

I am using mysql to retrieiving data.
While I am reading online documentation.
I found that someone have tested mysql_stmt_prepare method is faster than mysql_fetch_row.

Actually, is it preferring to use mysql_stmt instead of mysql_fetch_row in direct?
What is the difference? What kind of condition should be considered?

Thanks in advance!

laserlight

Are you talking about the MySQLi extension? In that case, note that the difference is between preparing a statement for possibly repeated execution versus executing it immediately.

If you only run the statement once, executing it immediately is faster, but then you lose out on the input escaping functionality of preparing statements. If you run the same statement multiple times but with different values, then preparing the statement and binding values is definitely better.

simongcc

Always thanks for your kindness, laserlight!

One point would like to clarify that why the document have not stated that clearly?
I read the online document to learn. Is there anything I have missed out or any other factors? Or I have to gain some more knowledge of? Any suggestion?

NogDog

As LL alluded to, if you are not going to use the query in a repeated fashion and/or make use of the ability to bind parameters and/or results, then the mysqli_stmt functionality does not buy you anything. Also, there are some differences in the details depending upon whether you are using the mysqli functions in an object-oriented or procedural manner, which at this point we do not know which way you are using them.

simongcc

Thanks ND, sorry for late reply. Just have a weekend holiday here.

Let me describe my situation.
1. I am using mysqli object-oriented style to write the code from opening the connection to whatever inside the script.
2. I am not sure if my case belongs to a repeating manner. Let me describe the case in abstract way.

I have a news column. I am creating a interface to manipulate(add/delete/read) news data from the DB. Because I build from scratch. So I start by creating a function to read the data. eg. read_news()
I call this read_news() every time I refresh or load the interface page.
Is it belongs to repeating use of query?

Thanks!

laserlight

No, since the use is repeated across requests, not within a single request. On the other hand,, if read_news() deals in user input, the escaping mechanism inherent in prepared statements can be useful, despite the slight inefficiency.

simongcc

Thanks LL. Now I understand what a repeated process is.

But I don't quite understand this statement.

...if read_news() deals in user input, the escaping mechanism inherent in prepared statements can be useful, despite the slight inefficiency.

What is "escaping mechanism"? And how to determine if it is not efficiency?
It is inefficiency in coding or processing? If it is inefficiency for processing, how to time it?

laserlight

What is "escaping mechanism"?

To prevent SQL injection.

And how to determine if it is not efficiency?

If you are escaping user input (or some other input), it is fine.

It is inefficiency in coding or processing?

What do you mean?

If it is inefficiency for processing, how to time it?

You can use microtime() to construct a primitive benchmarking script.