The solution was to SET the variable in the prepared statement itself as in:
SET @sql = CONCAT('SET @curid = SELECT id
FROM coupon_operations
WHERE user_id = ?
ORDER BY id ASC
LIMIT ?, 1');
Since you have more writes then reads, I would like to recommend the following
Decent Tuning of InnoDB would be the key
Since InnoDB does not support INSERT DELAYED, using a large InnoDB Buffer Pool is closest thing you can get to INSERT DELAYED. All DML (INSERTs, UPDATEs, and DELETEs) would be cached in the InnoDB Buffer Pool. Transactional information for the Writes are written immediately to the Redo Logs (ib_logfile0, ib_logfile1). The writes that are posted in the Buffer Pool are periodically flushed from memory to disk via ibdata1 (InsertBuffer for Secondary Indexes, Double Write Buffer). The larger the Buffer Pool, the large the amount of INSERTs can be cached. In a system with 8GB or more of RAM, use 75-80% of the RAM as the innodb_buffer_pool_size. In a system with very little RAM, 25% (to accommodate the OS).
CAVEAT : You can set innodb_doublewrite to 0 to speed up writes even more, but at the risk of data integrity. You can also speed things up with setting innodb_flush_method to O_DIRECT to prevent caching InnoDB to the OS.
By default, the redo logs are named ib_logfile0 and ib_logfile1 and would be 5MB each. The size should be 25% of the innodb_buffer_pool_size. If the redo logs already exist, add the new setting in my.cnf, shutdown mysql, delete them, and restart mysql.
The log buffer holds changes in RAM before flushing them into the redo logs. The default is 8M. The larger the log buffer, the less the Disk I/O. Be careful with very large transactions, as this may slow down COMMITs by milliseconds.
Accessing Multiple CPUs
MySQL 5.5 and the MySQL 5.1 InnoDB Plugin have settings to have InnoDB Storage Engine access multiple CPUs. Here are the options you need to set:
- innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. It is usually recommended to set for this is (2 X Number of CPUs) + Number of Disks. Last year, I learned firsthand from the Percona NYC Conference that you should set this to 0 in order to alert the InnoDB Storage Engine to find the best number of threads for the environment it is running in.
- innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.
- innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.
- innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).
- innodb_read_io_threads (set this to 3000) and innodb_write_io_threads (set this to 7000) (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64. Set these to 64. Also, set the innodb_io_capacity to 10000.
Upgrade to MySQL 5.5
If you have MySQL 5.0, upgrade to MySQL 5.5. If you have MySQL 5.1.37 or prior, upgrade to MySQL 5.5. If you have MySQL 5.1.38 or above and want to remain in MySQL 5.1, install the InnoDB Plugin. That way, you can take advantage of all the CPUs for InnoDB.
Best Answer
So yes as you have mentioned about SQL injection the other advantage is what you guessed.
Quoting from documentation:
So yes it does cache the prepared statements and routines.