I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice with a short interval in between, the query only takes about 90 ms. The query is in both cases the same. When I run the script, with the same query, after some time again, it takes 1500 ms again.
Why is that? Does the database cache automatically? Is there some time the database saves the cache and then automatically deletes it?
I assume the results can't be cached by PHP, because this happens in two different threads. I wouldn't think PHP would cache the results, because it can't know if the database has changed.
I have a script running every minute to insert new rows to the database. This might also be the reason that it takes 1500 ms again after some time; the cache would've been deleted, because the relevant tables aren't the same anymore.