I have a web application using PHP 7.0 and MySQL 5.5.54-0+deb8u1-log.

Occasionally, there are long waiting times in the application, which seem to be related to slow database queries. In the slow query log of MySQL, I can find some queries which have been executed unusually slow, though they normally are finished in milliseconds. I use MyISAM which is obviously prone for waiting for locks, but – what's strange is that the queries do not have to wait for locks at all.

Of course you can argue that the query is not optimized and all – regarding multiple joins, sorting, union all etc. But if I run the following example in the database while the application is on its load peak, it usually runs just nice and quick!

Here one example:

# Query_time: 31.449621  Lock_time: 0.000284 Rows_sent: 22  Rows_examined: 131
SET timestamp=1494921515;
            (SELECT
                table1.*,
                table2.userName,
                table2.userId,
                table2.userPictureId,
                table3.groupName,
                table3.viewOption,
                table3.force_private
            FROM table4
            INNER JOIN table1
                ON table4.userId1='192203'
                AND table4.userId2=table1.userId
            INNER JOIN table3
                ON table1.groupId=table3.groupId
                AND table3.deleted=0
            INNER JOIN table2
                ON table1.userId=table2.userId
                AND table2.class IN ('A','B')
                AND NOT table1.actionId='20'
            ORDER BY date DESC LIMIT 0,50)

            UNION ALL

            (SELECT
                table1.*,
                table2.userName,
                table2.userId,
                table2.userPictureId,
                table3.groupName,
                table3.viewOption,
                table3.force_private
            FROM table1
            INNER JOIN table3
                ON table1.groupId=table3.groupId
                AND table3.deleted=0
            INNER JOIN table2
                ON table1.userId=table2.userId
                AND table2.userId='192203'
                AND NOT table1.actionId='20'
            ORDER BY date DESC LIMIT 0,50)  

            ORDER BY date DESC LIMIT 0,100;

So – what I'm looking for is not a way to make this specific query faster. Of course it wouldn't hurt, but normally this specific query is finished in about 0,01s!

If this occurs, then there are usually some more normally fast queries which are executed slowly. I see it in the slow query log.

One observation is that the execution time seems always to be around 30 seconds – and the time limit for PHP scripts is 30 seconds in my case, so I wonder if there is any relation. If all related MySQL queries are automatically canceled when a PHP script is killed, then this would explain it. By the way, I use PDO to connect PHP to MySQL.

One idea I had is that maybe the actual query execution time is not so bad, but maybe MySQL has issues while trying to hand over the resultset to PHP – for an unknown reason. But on the other hand, I'm not sure if this waiting time would be added to the query execution time in the slow query log! Does anybody know? What else could be the problem?