I have code similar to the following in a php document that communicates with a mysql database:
$db = new PDO('mysql:host=127.0.0.1;dbname=db;charset=UTF-8', 'name', 'pw',array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_PERSISTENT => true, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true));
$stmt=$db->prepare("SELECT SUM(anything)
FROM table
WHERE date IN (SELECT date FROM table WHERE something=:something AND somethingElse=:somethingElse)
AND somethingElse=:somethingElse1");
This code takes a long time to run in a browser the first time through... if executed again via a refresh it runs over 100 times faster. I'm trying to figure out what causes a pdo statement like this to run so much faster on a refresh and can't figure it out.
That's mysql's query cache kicking in... as long as your RAM (read: MySQL cache memory allocation) is sufficiently large, this will happen to almost any SELECT.
That's just query cache.
It is not related to prepared statements.
You have to optimize your query anyway, to make it always run fast.
The server "cache" the execution your query. So the first time it take a lot of time but after that the system know how to resolved it.
To optimize, a couple quick points:
Firstly, something like this should not take long at all (even on massive tables) if you have correct indexing.
What are your tables, and how are the indexes setup. This will help us optimise your query.