I have a MySQL server 5.5 installed on an Ubuntu Server 14.04 x64 with 12GB of RAM and a partition of 8GB for swap.
I also have a Python (django) app with a while true that is querying a table with more than 50 million rows. The query is complex with four or five INNER JOIN -one of them to a subquery-, but it takes less than a second to be completed.
At the same time, I have an other complex query to the same table to be used in PHP (mysqli). The query is executed every 10 second through ajax (127.0.0.1).
I experienced the following scernario:
- If the python script is stopped, the PHP is doing the query very fast (0~10ms)
- If the python script is running, the first time the query of PHP is executed it takes a lot of time (~30s), but next times it does the job very fast (0~10ms)
In order to dismiss any problem related to the network, I tried the same scenario but using Toad software instead of PHP: The problem was the same.
Could be the MySQL cache? How could I discard it?
Thank you very much.