Prepared statements are considered to be the immediate statements of MySQL with advantages of client/server binary protocol. Before MySQL 4.1 version, the query that was sent to the MySQL server and returned data was in textual format. This textual protocol had a performance implication when a statement was being executed multiple times. Hence, from MySQL 4.1 prepared statements were introduced.

An immediate statement when processed parses the query for lexical and syntactical errors. The optimizer devises a strategy to execute the statement and get the required result. The actual execution of the statement happens with the retrieval/ writing of data with construction of result set. However these steps do not comes to the notice of the client and the result sets are displayed immediately.

Prepared statements are executed in three steps:

PREPARE

In this phase the statement is being prepared for execution. In the PREPARE statement, the query has placeholder '?' which binds the data value to the query while executing. The syntax of PREPARE statements is written like this:

PREPARE stmt1 FROM 'SELECT * 

FROM tblstudents 

WHERE rollNo = ?';

EXECUTE

The EXECUTE executes the statement prepared in the first step. In this step if there are any parameter markers it must be supplied with USING clause. The variables must match to the number of place holders in PREPARE statement. The prepared statement can be executed multiple times by passing different variables to it. The syntax to EXECUTE statement explained above is:

SET @id = '234';

EXECUTE stmt1 USING @id;

DEALLOCATE PREPARE

This statement releases the PREPARE statement. After the statement is de-allocated, it cannot be executed with the name. To de-allocate above statement syntax is:

DEALLOCATE PREPARE stmt1;

Advantage of prepared statements

The prepared statements are efficient and useful when a particular statement is executed many times for different variable values. The execution step is repeated with prepared statements and hence time is saved in without being the need to parse and build query each time the result is required. Efficiency is increased when there is execution of the statement and result set is not required as in UPDATE, INSERT, DELETE statements.