MySQL / MyISAM: Queries sometimes very slow, no waiting for lock time

Question

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?

Answer 1

If you are looking for the latest 100, you have not quite gotten it. You need LIMIT 100 in the inner queries, too. (OK, that will slow down the query a little, but it might be 'correct'.)

Back to performance...

Short answer: Switch to InnoDB; it is likely to help, especially in avoiding strange locks. (Instead, you will other strange locks, but usually not for as long.)

Which table is date in? Hard to analyze the query without having all columns qualified and SHOW CREATE TABLE. EXPLAIN SELECT ... would also help. But I will try...

Indexes needed:

table4: INDEX(userId1, userId2)  -- in this order
table1: INDEX(userId)
table3: INDEX(groupId)
table2: INDEX(userId, class)  -- in this order