Need some help here. MySql is crashing when a query gets executed. Query does a self join. Table has about 3 million rows in it. Its strange, sometimes it works, then for other users it crashes Mysql. Attached the query and the plan. The query tries to return the most recent messages in a message thread.
This keeps bringing our site (
http://text4freeonline.com ) down so any help would be greatly appreciated
SELECT `text_messages`.* FROM `text_messages` LEFT OUTER JOIN text_messages as m2 ON (text_messages.sms_thread_id = m2.sms_thread_id AND m2.queue_name = 'rachelesmith' AND (m2.is_deleted IS NULL OR m2.is_deleted = 0) AND (m2.hide_from_chat IS NULL OR m2.hide_from_chat = 0) AND text_messages.created_at < m2.created_at) WHERE (m2.sms_thread_id is NULL) AND (((text_messages.queue_name = 'rachelesmith') AND (text_messages.is_deleted IS NULL OR text_messages.is_deleted = 0)) AND (text_messages.hide_from_chat IS NULL OR text_messages.hide_from_chat = 0)) AND ( (`text_messages`.`type` = 'SmsTextMessage' ) ) ORDER BY text_messages.updated_at DESC LIMIT 0, 25
-----------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | text_messages | ref | index_text_messages_on_type,index_text_messages_on_queue_name,index_text_messages_on_queue_name_and_sms_thread_id,newindex | index_text_messages_on_queue_name | 258 | const | 4 | Using where; Using filesort |
| 1 | SIMPLE | m2 | ref | index_text_messages_on_created_at_and_type,index_text_messages_on_created_at,index_text_messages_on_queue_name,index_text_messages_on_queue_name_and_sms_thread_id,newindex,txt_msgs_fast,txt_msgs_fast2 | index_text_messages_on_queue_name | 258 | const | 4 | Using where |
+----+-------------+---------------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+
2 rows in set (0.00 sec)