MySQL queries still slow after deleting bunch of records

Question

I need some help with resolving some mysql issues. My website had been running slow since last week and after contacting my host I found out that some queries were taking too long mainly because of table locks. I am a developer but no mysql/database expert. My host suggested that I either delete and/or change the two tables in question to innoDB. So as these tables had a lot of junk data I decided to delete a bunch of records. I would say that these two tables are about 25% of the size when this problem started. Problem is that, it still has not made any difference. So my questions:

1. Do I need to clear cache or optimize table in order to see the effects? My host is still suggesting I change those tables to innoDB which is fine but I'm not sure why deleting that many records have not made a difference.
2. Also I have read that it is better to recreate the table than just optimizing? If needed I can hire a database admin to help me but I would like to at least try a few things if this is something simple. Can someone please guide me through this.

One more important thing to add, it is a legacy website running on php 5.4 and mysql 5.6

Here is one of the sample query that locks the table.

SELECT `m`.`message_id`, COUNT(`m`.`message_id`) AS `mails_count`, `m`.`sender_id`, `m`.`recipient_id`, 
            `m`.`text`, `m`.`is_readable`, `m`.`time_stamp` AS `last_message_ts`, `c`.`conversation_id`, `c`.*, `ms`.`is_replied`
            FROM `mailbox_conversation` AS `c` 
            INNER JOIN (
                SELECT * FROM `mailbox_message` 
                WHERE `recipient_id`=67404  AND IF (`sender_id`!=67404, `status`='a', 1) ORDER BY `time_stamp` DESC  
            ) AS `m` ON(`m`.`conversation_id`=`c`.`conversation_id`) 
            INNER JOIN (
                SELECT `conversation_id`, IF(`sender_id`=67404,'yes','no') AS `is_replied` FROM `mailbox_message` 
                WHERE (`recipient_id`=67404 OR `sender_id`=67404) ORDER BY `time_stamp` DESC 
            ) AS `ms` ON(`ms`.`conversation_id`=`c`.`conversation_id`)
            WHERE (`c`.`initiator_id`=67404 OR `interlocutor_id`=67404)
            AND `c`.`bm_deleted` NOT IN (IF(`c`.`initiator_id`=67404, '1, 3','2, 3'))           
             AND IF (`sender_id`!=67404, `status`='a', 1)
            GROUP BY `c`.`conversation_id`
            ORDER BY `m`.`time_stamp` DESC  LIMIT 0,15;

Thank you!

Answer 1

Here are the things that are slowing down your complex query:

(1)

    GROUP BY  `c`.`conversation_id`
    ORDER BY  `m`.`time_stamp` DESC
    LIMIT  0,15;

It would be nice if it could read only 15 rows. But that is not possible because the GROUP BY and ORDER BY don't match. Furthermore, they involve multiple tables, thereby eliminating using an index. (An INDEX can reference only one table.)

(2)

AND  IF (`sender_id`!=67404, `status`='a', 1)

(`recipient_id`=67404  OR  `sender_id`=67404)

(`c`.`initiator_id`=67404  OR  `interlocutor_id`=67404)

Both of those are too complex to use any index. OR is a performance killer; it can sometimes be turned into a UNION. (But you would need nested UNIONs to handle everything here.) That if can probably be written sender_id = 67404 or status='a' -- it won't be faster, but it will be clearer (to me).

One case:

( SELECT ... from mailbox_message WHERE `recipient_id`=67404 )
UNION ALL
( SELECT ... from mailbox_message WHERE `sender_id`=67404 )

Needed for this UNION: INDEX(recipient_id) and INDEX(sender_id).

(3)

JOIN ( SELECT ... )

This, especially when there are multiple such, is usually un-optimizable.

(4)

JOIN ( SELECT ... ORDER BY ... )

The ORDER BY will be ignored. Cf ONLY_FULL_GROUP_BY.

(5)

SELECT *

If there are any bulky columns that you don't ultimately need, * may cost some performance. Spell out the columns needed.