Are there ways of increasing the execution speed of mysqli->prepare?

My tests are running pretty slow... I've got under 100 tests, but it still takes over 10 seconds开发者_开发百科 to run on a fast machine. I tried disabling code coverage, and using caching for the couple of things I do that hit remote servers (e.g. LDAP).

Profiling the tests while PHPUnit is running produces:

fully 10% of the running time -- which includes the time to gather info for and generate a lot of pages for code coverage -- is spent in mysqli->prepare. Unfortunately, I can't abstract the database out here. Not only am I effectively testing a database access layer itself, a lot of logic is implemented in terms of JOINs inside the code under test.

Are there any configuration settings I can change or anything of that nature to make this faster? Does prepare require calling out to the mysql server (and therefore setting up a local mysql server for testing might help?)

Yes, a prepare() statement will call out to the MySQL server. Prepared statements are executed server-side in two calls: a PREPARE call, and an EXECUTE call.

Per example:

mysql> PREPARE stmt1 FROM 'SELECT * FROM mysql.user';
mysql> EXECUTE stmt1;

Same goes for any driver, whether it is the PHP driver of a JDBC driver. To further prove that it in fact does make a call, execute this code:

$mysqli->prepare('SELECT * FROM mysql.user');

...and you can see the following in MySQL:

mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+--------------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                     |
+-----+------+-----------+------+---------+------+-------+--------------------------+
| 564 | root | localhost | NULL | Query   |    0 | NULL  | show processlist         |
| 565 | root | localhost | NULL | Prepare |    0 | NULL  | SELECT * FROM mysql.user |
+-----+------+-----------+------+---------+------+-------+--------------------------+
2 rows in set (0.00 sec)

To accelerate this, you can use UNIX domain sockets on the local machine (assuming you have a POSIX OS). This removes the overhead of TCP handshaking and error correction, as well as network lag since it is executed locally.

The best solution, however, is in fact to abstract your database connections using mock objects, but you already knew that. A unit test is not a unit test if it must connect to a database: it's an integration test.