php - Why MySQLi prepared statements?

Question

What are the advantages of using prepared statements with MySQLi?

If the only purpose is to secure the query, isn't it better to clean the query using something like mysqli_real_escape_string instead of writing so many lines of code for each query (like prepare, bind_param, execute, close, etc.)?

Answer 1

Preparing statements is not just for code security. It helps the SQL server parse your statement and generate an execution plan for your query.

If you run a SELECT 1000 times then the SQL server will have to parse, prepare, and generate a plan on how to get your data 1000 times.

If you prepare a statement then run the prepared statement 1,000 times each with different bound values then the statement is parsed only once and the same query plan is used each time.

This helps not only when you run 1,000 queries inside the script but also if you only have 1 statement and run the script 1,000 times. The server can remember the plan server side. The next time your script runs it will use the same plan again.

Sure it may seem trivial for one or two queries but when you start executing large numbers of queries or the same query repeatedly you will save a lot of processing time.