3

Possible Duplicate:
Are prepared statements cached server-side across multiple page loads with PHP?

I'm working on a new project and using parameterized queries for the first time (PHP with a MySQL DB). I read that they parameterized queries are cached, but I'm wondering how long they are cached for. For example, let's say I have a function 'getAllUsers()' that gets a list of all active user ID's from the user table and for each ID, a User object is created and a call to function 'getUser($user)' is made to set the other properties of the object. The 'getUser()' function has it's own prepared query with a stmt->close() at the end of the function.

If I do it this way, does my parameterized query in 'getUser()' take advantage of caching at all or is the query destroyed from cache after each stmt->close()?

Note: I also use the getUser() function if a page only requires data for a single user object so I wanted to do it this way to ensure that if the user table changes I only ever need to update one query.

Is this the right way of doing something like this or is there a better way?

Update: Interesting, just saw this on php.net's manual for prepared statements (http://php.net/manual/en/mysqli.quickstart.prepared-statements.php)

Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement.

So I guess the main benefit for parameterized queries is to protect against SQL injection and not necessarily to speed things up unless it's a query that will repeated at one time.

Community
  • 1
  • 1
Felix
  • 610
  • 2
  • 9
  • 21
  • Ok after reading up on something else I'm seeing quite a few ppl say that I shouldn't use stmt->free_result() or stmt->close() unless it's a long script that consumres a lot of memory, is this true, should I just not use them? – Felix Dec 20 '12 at 03:16
  • @JasonMcCreary, this is not the same question. This is if I wanted to display a list of users (or anything else) on a page that has one query getAllUsers() to select all ID's and then within that function multiple calls to the function getUser(). So this is all happening in one page load. – Felix Dec 20 '12 at 03:20
  • Can you define "cached" please? Can you also cite some sources on the whole "cache" thing? Prepared statements in *real* databases (read: not MySQL) have some advanced magic they can do to prepared statements, but I've never seen any *caching* go on because of them. I expect you're either using the wrong term and are thus confused, or have been reading disreputable things and are thus confused. – Charles Dec 20 '12 at 04:38

2 Answers2

3

Calling mysqli_stmt::close will:

Closes a prepared statement. mysqli_stmt_close() also deallocates the statement handle.

therefore not being able to use the cached version of the statement for further executions. I wouldn't mind of freeing resources or closing statements since PHP will do it for you at the end of the script anyway.

Also if you are working with loops (as you described) take a look at mysqli_stmt::reset which will reset the prepared statement to its original state (after the prepare call).

Shoe
  • 74,840
  • 36
  • 166
  • 272
  • Thanks Jeffrey! Just what I was looking for, and bonus props for the added tidbit about mysqli_stmt::reset – Felix Dec 20 '12 at 05:07
0

That's good question, from some point of view.

First, about "caching".
There is some special thing about prepared queries - you can send it to server once and then execute it multiple times. It can give some small theoretical benefit for using already parsed and prepared query.
As it seems, you're not using such mechanism, every time preparing every your query. So, there is no caching at all.

Next, about premature optimization.
You've heard of some caching, and it occupied your imagination.
While there is no real need or cause for you to concern about caching or whatever performance issue.

So, there is a rule: do not occupy yourself with performance issues until they are real.
Otherwise you'll waste your time.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • So in my example, would I be better off passing in an array of user objects to the 'getUser()' function (even if it were just one) and prepare the statement, then loop through the array, changing the parameters for each object required? – Felix Dec 20 '12 at 18:56