Before and After: Querying and Retrieving Data with Prepared Statements

One way to speed up MySQL is to use prepared statements. Every time you make an SQL query, MySQL parses the request, checks its validity, and executes it. While MySQL does this efficiently, prepared statements make this process even faster.

A prepared statement is a way to tell MySQL what your query will look like before you actually execute it. This description doesn’t contain the exact query; it’s more like a query template. Most of the query is hardcoded, but there are placeholders where you want to customize the information.

You pass this template to MySQL. It parses and validates the query, and returns a statement handle. You then use that handle to execute the request.

In contrast to a traditional query, when you execute this type of request, there’s no need to parse the SQL. As a result, MySQL executes the query faster. If you make the same query more than once, it’s faster to use a prepared statement than a direct query. Additionally, prepared statements automatically escape quotes, so you don’t need to worry about stray characters. This is a big benefit that makes prepared statements worthwhile even for single queries.

Prepared statements also let you control data retrieval. There’s no longer a need to first retrieve a row into an array and then assign each element to an individual variable. You can instruct MySQL to place each piece of data directly into a variable.

PHP 4: Traditional Input Queries ...

Get Upgrading to PHP 5 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.