Clear MySQL Cache

Question

I took over a project written in Laravel 4. We have MySQL 5.6.21 - PHP 5.4.30 - currently running on Windows 8.1.

Every morning on the first attempt to access the landingpage - which contain about 5 queries on the backend - this site will crash with a php-timeout (over 30 seconds for response).

After using following I got closer to the cause: Laravel 4 - logging SQL queries. One of the queries takes more than 25 seconds on the first call. After that its always < 0.5 seconds.

The query has got 3 joins and 2 subselects wrapped in Cache::remember. I want to go into optimizing this so that on production it won't run into this problem.

So I want to test different SQLs The Problem is that the first time the data gets cached somehow and then I can't see whether my new SQL's are better or not.

Now, since I guess it's a caching issue (on the first attempt it takes long, afterwards not) I did these:

MySQL: FLUSH TABLES;
restart MySQL
restart Apache
php artisan cache:clear

But still, the query works fast. Then after some time I don't access the database at all (can't give an exact time, maybe 4 hours of inactivity) it happens again.

Explain says:

1 | Primary | table1 | ALL | 2 possible keys | NULL | ... | 1010000 | using where; using temporary; using filesort
1 | Primary | table2 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table3 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table4 | eq_ref | PRIMARY | PRIMARY | ... | 1 | NULL
3 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where
2 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where

So here the questions:

• What's the reason for this long time?
• How can I reproduce it? and
• Is there a way to fix it?

I read mysql slow on first query, then fast for related queries. However that doesn't answer my question on how to reproduce this behaviour.

---

Update

I changed the SQL and now it is written like:

select 
    count(ec.id) as asdasda

from table1 ec force index for join (PRIMARY)
    left join table2 e force index for join (PRIMARY) on ec.id = e.id
    left join table3 v force index for join (PRIMARY) on e.id = v.id 

where
    v.col1 = 'aaa'
    and v.col2 = 'bbb'
    and v.col3 = 'ccc'
    and e.datecol > curdate()
    and e.col1 != 0

Now explain says:

+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
| id | select_type | table  | type   | possible_keys | key          | key_len | ref             | rows   | Extra       |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
|  1 | SIMPLE      | table3 | ALL    | PRIMARY       | NULL         | NULL    | NULL            | 114032 | Using where |
|  1 | SIMPLE      | table2 | ref    | PRIMARY       | PRIMARY      | 5       | table3.id       |     11 | Using where |
|  1 | SIMPLE      | table1 | eq_ref | PRIMARY       | PRIMARY      | 4       | table2.id       |      1 | Using index |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+

Is that as good as it can get?

Answer 1

The data might be cached in the InnoDB buffer pool or on Windows filesystem cache.

You can't explicitly flush the InnoDB cache but you can set the flushing parameters to more aggressive values:

SET GLOBAL innodb_old_blocks_pct = 5
SET GLOBAL innodb_max_dirty_pages_pct = 0

You can use the solution provided here to clear Windows filesystem cache: Clear file cache to repeat performance testing

But what you really need is an index on table3 (col1, col2, col3)