Optimising user message search ============================== Lots of examples of this query showing up in the slow query log. Typical query times around 20 seconds, although this particular example was for 48 seconds. There are a number of things that sticks out immediately (aside from the time): Doing a sort on a timestamp when there is a perfectly good AUTO_INC, can cause an additional sort There's a rudimentary paging mechanism that will materialize the same rows for each run which will be an issue with large result sets The use of 2 wildcards on a string precludes the use of an index Wrapping a lookup in a function also prevents index use Those things aside, the big problem is the query considers 10m rows when it's looking for records against a single userid. # Time: 2022-05-11T20:03:00.573316Z # Query_time: 48.431146 Lock_time: 0.000639 Rows_sent: 0 Rows_examined: 10045031 SET timestamp=1652299332; SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, m.useridfrom, mcm.userid as useridto, m.subject, m.fullmessage, m.fullmessagehtml, m.fullmessageformat, m.smallmessage, m.conversationid, m.timecreated, 0 as isread, u.id AS userfrom_id,u.picture AS userfrom_picture,u.firstname AS userfrom_firstname,u.lastname AS userfrom_lastname,u.firstnamephonetic AS userfrom_firstnamephonetic,u.lastnamephonetic AS userfrom_lastnamephonetic,u.middlename AS userfrom_middlename,u.alternatename AS userfrom_alternatename,u.imagealt AS userfrom_imagealt,u.email AS userfrom_email,u.lastaccess AS userfrom_lastaccess, mub.id as userfrom_blocked, u2.id AS userto_id,u2.picture AS userto_picture,u2.firstname AS userto_firstname,u2.lastname AS userto_lastname,u2.firstnamephonetic AS userto_firstnamephonetic,u2.lastnamephonetic AS userto_lastnamephonetic,u2.middlename AS userto_middlename,u2.alternatename AS userto_alternatename,u2.imagealt AS userto_imagealt,u2.email AS userto_email,u2.lastaccess AS userto_lastaccess, mub2.id as userto_blocked FROM mdl_messages m INNER JOIN mdl_user u ON u.id = m.useridfrom INNER JOIN mdl_message_conversations mc ON mc.id = m.conversationid INNER JOIN mdl_message_conversation_members mcm ON mcm.conversationid = m.conversationid INNER JOIN mdl_user u2 ON u2.id = mcm.userid LEFT JOIN mdl_message_users_blocked mub ON (mub.blockeduserid = u.id AND mub.userid = '293836') LEFT JOIN mdl_message_users_blocked mub2 ON (mub2.blockeduserid = u2.id AND mub2.userid = '293836') LEFT JOIN mdl_message_user_actions mua ON (mua.messageid = m.id AND mua.userid = '293836' AND mua.action = '2') WHERE (m.useridfrom = '293836' OR mcm.userid = '293836') AND (m.useridfrom != mcm.userid OR mc.type = '3') AND u.deleted = 0 AND u2.deleted = 0 AND mua.id is NULL AND LOWER(smallmessage) LIKE LOWER('%turnitin%') COLLATE utf8mb4_bin ESCAPE '\\' ORDER BY timecreated DESC LIMIT 0, 51; MySQL SQL > EXPLAIN SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, m.useridfrom, mcm.userid as useridto, m.subject, m.fullmessage, -> m.fullmessagehtml, m.fullmessageformat, m.smallmessage, m.conversationid, m.timecreated, 0 as isread, -> u.id AS userfrom_id,u.picture AS userfrom_picture,u.firstname AS userfrom_firstname,u.lastname AS userfrom_lastname,u.firstnamephonetic AS userfrom_firstnamephonetic,u.lastnamephonetic AS userfrom_lastnamephonetic,u.middlename AS userfrom_middlename,u.alternatename AS userfrom_alternatename,u.imagealt AS userfrom_imagealt,u.email AS userfrom_email,u.lastaccess AS userfrom_lastaccess, mub.id as userfrom_blocked, u2.id AS userto_id,u2.picture AS userto_picture,u2.firstname AS userto_firstname,u2.lastname AS userto_lastname,u2.firstnamephonetic AS userto_firstnamephonetic,u2.lastnamephonetic AS userto_lastnamephonetic,u2.middlename AS userto_middlename,u2.alternatename AS userto_alternatename,u2.imagealt AS userto_imagealt,u2.email AS userto_email,u2.lastaccess AS userto_lastaccess, mub2.id as userto_blocked -> FROM mdl_messages m -> INNER JOIN mdl_user u -> ON u.id = m.useridfrom -> INNER JOIN mdl_message_conversations mc -> ON mc.id = m.conversationid -> INNER JOIN mdl_message_conversation_members mcm -> ON mcm.conversationid = m.conversationid -> INNER JOIN mdl_user u2 -> ON u2.id = mcm.userid -> LEFT JOIN mdl_message_users_blocked mub -> ON (mub.blockeduserid = u.id AND mub.userid = '293836') -> LEFT JOIN mdl_message_users_blocked mub2 -> ON (mub2.blockeduserid = u2.id AND mub2.userid = '293836') -> LEFT JOIN mdl_message_user_actions mua -> ON (mua.messageid = m.id AND mua.userid = '293836' AND mua.action = '2') -> WHERE (m.useridfrom = '293836' OR mcm.userid = '293836') -> AND (m.useridfrom != mcm.userid OR mc.type = '3') -> AND u.deleted = 0 -> AND u2.deleted = 0 -> AND mua.id is NULL -> AND LOWER(smallmessage) LIKE LOWER('%turnitin%') COLLATE utf8mb4_bin ESCAPE '\\' -> ORDER BY timecreated DESC LIMIT 0, 51; +----+-------------+-------+------------+--------+--------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------+--------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+--------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------+--------+----------+----------------------------------------------+ | 1 | SIMPLE | mc | NULL | index | PRIMARY,mdl_messconv_typ_ix | mdl_messconv_typ_ix | 8 | NULL | 556912 | 100 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | mcm | NULL | ref | mdl_messconvmemb_con_ix,mdl_messconvmemb_use_ix | mdl_messconvmemb_con_ix | 8 | livemoodle.mc.id | 1 | 100 | NULL | | 1 | SIMPLE | u2 | NULL | eq_ref | PRIMARY,mdl_user_del_ix | PRIMARY | 8 | livemoodle.mcm.userid | 1 | 50 | Using where | | 1 | SIMPLE | m | NULL | ref | mdl_mess_use_ix,mdl_mess_con_ix,mdl_mess_contim_ix | mdl_mess_con_ix | 8 | livemoodle.mc.id | 3 | 100 | Using where | | 1 | SIMPLE | u | NULL | eq_ref | PRIMARY,mdl_user_del_ix | PRIMARY | 8 | livemoodle.m.useridfrom | 1 | 50 | Using where | | 1 | SIMPLE | mub | NULL | eq_ref | mdl_messuserbloc_useblo_uix,mdl_messuserbloc_use_ix,mdl_messuserbloc_blo_ix | mdl_messuserbloc_useblo_uix | 16 | const,livemoodle.m.useridfrom | 1 | 100 | Using where; Using index | | 1 | SIMPLE | mub2 | NULL | eq_ref | mdl_messuserbloc_useblo_uix,mdl_messuserbloc_use_ix,mdl_messuserbloc_blo_ix | mdl_messuserbloc_useblo_uix | 16 | const,livemoodle.mcm.userid | 1 | 100 | Using where; Using index | | 1 | SIMPLE | mua | NULL | eq_ref | mdl_messuseracti_usemesact_uix,mdl_messuseracti_use_ix,mdl_messuseracti_mes_ix | mdl_messuseracti_usemesact_uix | 24 | const,livemoodle.m.id,const | 1 | 10 | Using where; Not exists; Using index | +----+-------------+-------+------------+--------+--------------------------------------------------------------------------------+--------------------------------+---------+-------------------------------+--------+----------+----------------------------------------------+ 8 rows in set (0.0338 sec) The EXPLAIN plan starts with an index scan against mdl_message_conversations, and starts joining rows from there MySQL SQL > select count(*) from mdl_message_conversations; +----------+ | count(*) | +----------+ | 733599 | +----------+ 1 row in set (0.3491 sec) That 3/4 million values. It uses the index on `type`, not because it's going to consider type = '3' which is in the WHERE clause as it can't select on that basis, but because that's the smallest index and it has to read the least data. The query then works it's way through a series of JOINs via ref and eq_ref access resulting in millions of rows considered. The resluting matching rows would then be written to a tmp table and sorted (none in the example above). In action the query runs as follows MySQL SQL > SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, m.useridfrom, mcm.userid as useridto, m.subject, m.fullmessage, -> m.fullmessagehtml, m.fullmessageformat, m.smallmessage, m.conversationid, m.timecreated, 0 as isread, -> u.id AS userfrom_id,u.picture AS userfrom_picture,u.firstname AS userfrom_firstname,u.lastname AS userfrom_lastname,u.firstnamephonetic AS userfrom_firstnamephonetic,u.lastnamephonetic AS userfrom_lastnamephonetic,u.middlename AS userfrom_middlename,u.alternatename AS userfrom_alternatename,u.imagealt AS userfrom_imagealt,u.email AS userfrom_email,u.lastaccess AS userfrom_lastaccess, mub.id as userfrom_blocked, u2.id AS userto_id,u2.picture AS userto_picture,u2.firstname AS userto_firstname,u2.lastname AS userto_lastname,u2.firstnamephonetic AS userto_firstnamephonetic,u2.lastnamephonetic AS userto_lastnamephonetic,u2.middlename AS userto_middlename,u2.alternatename AS userto_alternatename,u2.imagealt AS userto_imagealt,u2.email AS userto_email,u2.lastaccess AS userto_lastaccess, mub2.id as userto_blocked -> FROM mdl_messages m -> INNER JOIN mdl_user u -> ON u.id = m.useridfrom -> INNER JOIN mdl_message_conversations mc -> ON mc.id = m.conversationid -> INNER JOIN mdl_message_conversation_members mcm -> ON mcm.conversationid = m.conversationid -> INNER JOIN mdl_user u2 -> ON u2.id = mcm.userid -> LEFT JOIN mdl_message_users_blocked mub -> ON (mub.blockeduserid = u.id AND mub.userid = '293836') -> LEFT JOIN mdl_message_users_blocked mub2 -> ON (mub2.blockeduserid = u2.id AND mub2.userid = '293836') -> LEFT JOIN mdl_message_user_actions mua -> ON (mua.messageid = m.id AND mua.userid = '293836' AND mua.action = '2') -> WHERE (m.useridfrom = '293836' OR mcm.userid = '293836') -> AND (m.useridfrom != mcm.userid OR mc.type = '3') -> AND u.deleted = 0 -> AND u2.deleted = 0 -> AND mua.id is NULL -> AND LOWER(smallmessage) LIKE LOWER('%turnitin%') COLLATE utf8mb4_bin ESCAPE '\\' -> ORDER BY timecreated DESC LIMIT 0, 51; Empty set (17.2786 sec) MySQL SQL > show status like 'handler%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 16 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 3537285 | <<<<<<< Huge number of lookups | Handler_read_last | 0 | | Handler_read_next | 8485347 | <<<<<<< | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 1 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | <<<<<<< Nothing goes to temp +----------------------------+---------+ 18 rows in set (0.0022 sec) MySQL SQL > show profile for query 2; +--------------------------------+-----------+ | Status | Duration | +--------------------------------+-----------+ | starting | 0.000374 | | Executing hook on transaction | 0.000026 | | starting | 0.000022 | | checking permissions | 0.000018 | | checking permissions | 0.000017 | | checking permissions | 0.000014 | | checking permissions | 0.000016 | | checking permissions | 0.000013 | | checking permissions | 0.000012 | | checking permissions | 0.000013 | | checking permissions | 0.000015 | | Opening tables | 0.000255 | | init | 0.000023 | | System lock | 0.000023 | | optimizing | 0.000063 | | statistics | 0.000820 | | preparing | 0.000080 | | Creating tmp table | 0.000131 | | executing | 17.276042 | <<<<<<<<<<<<<<<<< run on RC | end | 0.000033 | | query end | 0.000008 | | waiting for handler commit | 0.000016 | | removing tmp table | 0.000013 | | waiting for handler commit | 0.000009 | | closing tables | 0.000021 | | freeing items | 0.000097 | | logging slow query | 0.000056 | | cleaning up | 0.000022 | +--------------------------------+-----------+ 28 rows in set (0.0008 sec) The difficulty for the query comes in the complexity of the WHERE clause and the fact that it does an OR test for values in 3 different tables, so it basically has to get the all the data from all of them to see if they will apply. Fortunately, we can short circuit the WHERE to focus only the messages for the user by finding them via a separate query. REWRITE ======= The UNION I've added below does this job for us and gets the message ids for only those records sent or received by the userid. Both halves of the UNION can use an index for the user id. UNION also removes duplicates which makes one of the WHERE tests redundant. The remainder of the query is identical to the existing one (so that it's obvious that the logic of the query is identical) except it uses the message id (PRIMARY KEY) to avoid an additional sort. We do still create a temp table for the UNION, unavoidable, but we have pre-filtered the rows by this method. The JOINs and complex WHERE conditions are then applied to a small fraction of the rows previously considered. In the example below this removes 10 million rows from consideration. The new version runs in 4 milliseconds. SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, m.useridfrom, mcm.userid as useridto, m.subject, m.fullmessage, m.fullmessagehtml, m.fullmessageformat, m.smallmessage, m.conversationid, m.timecreated, 0 as isread, u.id AS userfrom_id,u.picture AS userfrom_picture,u.firstname AS userfrom_firstname,u.lastname AS userfrom_lastname,u.firstnamephonetic AS userfrom_firstnamephonetic,u.lastnamephonetic AS userfrom_lastnamephonetic,u.middlename AS userfrom_middlename,u.alternatename AS userfrom_alternatename,u.imagealt AS userfrom_imagealt,u.email AS userfrom_email,u.lastaccess AS userfrom_lastaccess, mub.id as userfrom_blocked, u2.id AS userto_id,u2.picture AS userto_picture,u2.firstname AS userto_firstname,u2.lastname AS userto_lastname,u2.firstnamephonetic AS userto_firstnamephonetic,u2.lastnamephonetic AS userto_lastnamephonetic,u2.middlename AS userto_middlename,u2.alternatename AS userto_alternatename,u2.imagealt AS userto_imagealt,u2.email AS userto_email,u2.lastaccess AS userto_lastaccess, mub2.id as userto_blocked FROM (SELECT m2.id AS 'id' FROM mdl_messages m2 WHERE m2.useridfrom = 293836 UNION SELECT m3.id AS 'id' FROM mdl_message_conversation_members mcm3 INNER JOIN mdl_messages m3 ON mcm3.conversationid = m3.conversationid WHERE mcm3.userid = 293836) der INNER JOIN mdl_messages m ON der.id = m.id INNER JOIN mdl_user u ON u.id = m.useridfrom INNER JOIN mdl_message_conversations mc ON mc.id = m.conversationid INNER JOIN mdl_message_conversation_members mcm ON mcm.conversationid = m.conversationid INNER JOIN mdl_user u2 ON u2.id = mcm.userid LEFT JOIN mdl_message_users_blocked mub ON (mub.blockeduserid = u.id AND mub.userid = '293836') LEFT JOIN mdl_message_users_blocked mub2 ON (mub2.blockeduserid = u2.id AND mub2.userid = '293836') LEFT JOIN mdl_message_user_actions mua ON (mua.messageid = m.id AND mua.userid = '293836' AND mua.action = '2') WHERE (m.useridfrom = '293836' OR mcm.userid = '293836') AND (m.useridfrom != mcm.userid OR mc.type = '3') AND u.deleted = 0 AND u2.deleted = 0 AND mua.id is NULL AND LOWER(m.smallmessage) LIKE LOWER('%turnitin%') COLLATE utf8mb4_bin ESCAPE '\\' ORDER BY m.id DESC LIMIT 0, 51; MySQL SQL > EXPLAIN SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, m.useridfrom, mcm.userid as useridto, m.subject, m.fullmessage, WHERE (m.useridfrom = '293836' OR mcm.userid = '293836') -> m.fullmessagehtml, m.fullmessageformat, m.smallmessage, m.conversationid, m.timecreated, 0 as isread, -> u.id AS userfrom_id,u.picture AS userfrom_picture,u.firstname AS userfrom_firstname,u.lastname AS userfrom_lastname,u.firstnamephonetic AS userfrom_firstnamephonetic,u.lastnamephonetic AS userfrom_lastnamephonetic,u.middlename AS userfrom_middlename,u.alternatename AS userfrom_alternatename,u.imagealt AS userfrom_imagealt,u.email AS userfrom_email,u.lastaccess AS userfrom_lastaccess, mub.id as userfrom_blocked, u2.id AS userto_id,u2.picture AS userto_picture,u2.firstname AS userto_firstname,u2.lastname AS userto_lastname,u2.firstnamephonetic AS userto_firstnamephonetic,u2.lastnamephonetic AS userto_lastnamephonetic,u2.middlename AS userto_middlename,u2.alternatename AS userto_alternatename,u2.imagealt AS userto_imagealt,u2.email AS userto_email,u2.lastaccess AS userto_lastaccess, mub2.id as userto_blocked -> FROM -> (SELECT m2.id AS 'id' -> FROM mdl_messages m2 -> WHERE m2.useridfrom = 293836 -> UNION ALL -> SELECT m3.id AS 'id' -> FROM mdl_message_conversation_members mcm3 -> INNER JOIN mdl_messages m3 ON mcm3.conversationid = m3.conversationid -> WHERE mcm3.userid = 293836) der -> INNER JOIN mdl_messages m ON der.id = m.id -> INNER JOIN mdl_user u ON u.id = m.useridfrom -> INNER JOIN mdl_message_conversations mc ON mc.id = m.conversationid -> INNER JOIN mdl_message_conversation_members mcm ON mcm.conversationid = m.conversationid -> INNER JOIN mdl_user u2 ON u2.id = mcm.userid -> LEFT JOIN mdl_message_users_blocked mub ON (mub.blockeduserid = u.id AND mub.userid = '293836') -> LEFT JOIN mdl_message_users_blocked mub2 ON (mub2.blockeduserid = u2.id AND mub2.userid = '293836') -> LEFT JOIN mdl_message_user_actions mua ON (mua.messageid = m.id AND mua.userid = '293836' AND mua.action = '2') -> WHERE (m.useridfrom = '293836' OR mcm.userid = '293836') -> AND (m.useridfrom != mcm.userid OR mc.type = '3') -> AND u.deleted = 0 -> AND u2.deleted = 0 -> AND mua.id is NULL -> AND LOWER(m.smallmessage) LIKE LOWER('%turnitin%') COLLATE utf8mb4_bin ESCAPE '\\' -> ORDER BY m.id DESC LIMIT 0, 51; +----+-------------+------------+------------+--------+--------------------------------------------------------------------------------+--------------------------------+---------+------------------------------+------+----------+--------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+--------------------------------------------------------------------------------+--------------------------------+---------+------------------------------+------+----------+--------------------------------------+ | 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100 | Using temporary; Using filesort | | 1 | PRIMARY | m | NULL | eq_ref | PRIMARY,mdl_mess_use_ix,mdl_mess_con_ix,mdl_mess_contim_ix | PRIMARY | 8 | der.id | 1 | 100 | Using where | | 1 | PRIMARY | u | NULL | eq_ref | PRIMARY,mdl_user_del_ix | PRIMARY | 8 | rcmoodle.m.useridfrom | 1 | 50 | Using where | | 1 | PRIMARY | mub | NULL | eq_ref | mdl_messuserbloc_useblo_uix,mdl_messuserbloc_use_ix,mdl_messuserbloc_blo_ix | mdl_messuserbloc_useblo_uix | 16 | const,rcmoodle.m.useridfrom | 1 | 100 | Using where; Using index | | 1 | PRIMARY | mc | NULL | eq_ref | PRIMARY,mdl_messconv_typ_ix | PRIMARY | 8 | rcmoodle.m.conversationid | 1 | 100 | NULL | | 1 | PRIMARY | mcm | NULL | ref | mdl_messconvmemb_con_ix,mdl_messconvmemb_use_ix | mdl_messconvmemb_con_ix | 8 | rcmoodle.m.conversationid | 1 | 100 | Using where | | 1 | PRIMARY | u2 | NULL | eq_ref | PRIMARY,mdl_user_del_ix | PRIMARY | 8 | rcmoodle.mcm.userid | 1 | 50 | Using where | | 1 | PRIMARY | mub2 | NULL | eq_ref | mdl_messuserbloc_useblo_uix,mdl_messuserbloc_use_ix,mdl_messuserbloc_blo_ix | mdl_messuserbloc_useblo_uix | 16 | const,rcmoodle.mcm.userid | 1 | 100 | Using where; Using index | | 1 | PRIMARY | mua | NULL | eq_ref | mdl_messuseracti_usemesact_uix,mdl_messuseracti_use_ix,mdl_messuseracti_mes_ix | mdl_messuseracti_usemesact_uix | 24 | const,der.id,const | 1 | 10 | Using where; Not exists; Using index | | 2 | DERIVED | m2 | NULL | ref | mdl_mess_use_ix | mdl_mess_use_ix | 8 | const | 1 | 100 | Using index | | 3 | UNION | mcm3 | NULL | ref | mdl_messconvmemb_con_ix,mdl_messconvmemb_use_ix | mdl_messconvmemb_use_ix | 8 | const | 1 | 100 | NULL | | 3 | UNION | m3 | NULL | ref | mdl_mess_con_ix,mdl_mess_contim_ix | mdl_mess_contim_ix | 8 | rcmoodle.mcm3.conversationid | 4 | 100 | Using index | +----+-------------+------------+------------+--------+--------------------------------------------------------------------------------+--------------------------------+---------+------------------------------+------+----------+--------------------------------------+ 12 rows in set (0.0028 sec) MySQL SQL > show status like 'handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 22 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 3 | | Handler_read_last | 0 | | Handler_read_next | 1 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 2 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 18 rows in set (0.0022 sec) MySQL SQL > show profile for query 6; +--------------------------------+----------+ | Status | Duration | +--------------------------------+----------+ | starting | 0.000449 | | Executing hook on transaction | 0.000025 | | starting | 0.000021 | | checking permissions | 0.000018 | | checking permissions | 0.000014 | | checking permissions | 0.000013 | | checking permissions | 0.000012 | | checking permissions | 0.000012 | | checking permissions | 0.000013 | | checking permissions | 0.000012 | | checking permissions | 0.000013 | | checking permissions | 0.000012 | | checking permissions | 0.000011 | | checking permissions | 0.000013 | | Opening tables | 0.000449 | | init | 0.000029 | | System lock | 0.000030 | | optimizing | 0.000013 | | optimizing | 0.000019 | | statistics | 0.000130 | | preparing | 0.000033 | | optimizing | 0.000019 | | statistics | 0.000119 | | preparing | 0.000136 | | statistics | 0.000947 | | preparing | 0.000085 | | Creating tmp table | 0.000154 | | executing | 0.000185 | | end | 0.000016 | | query end | 0.000012 | | waiting for handler commit | 0.000022 | | removing tmp table | 0.000016 | | waiting for handler commit | 0.000013 | | removing tmp table | 0.000012 | | waiting for handler commit | 0.000012 | | removing tmp table | 0.000011 | | waiting for handler commit | 0.000010 | | closing tables | 0.000028 | | freeing items | 0.000129 | | cleaning up | 0.000044 | +--------------------------------+----------+ 40 rows in set (0.0010 sec) Examples actually returning data ================================ 283702 as userid %world% as string MySQL SQL > SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, m.useridfrom, mcm.userid as useridto, m.subject, m.fullmessage, INNER JOIN mdl_message_conversation_members mcm ON mcm.conversationid = m.conversationid -> m.fullmessagehtml, m.fullmessageformat, m.smallmessage, m.conversationid, m.timecreated, 0 as isread, LEFT JOIN mdl_message_user_actions mua -> u.id AS userfrom_id,u.picture AS userfrom_picture,u.firstname AS userfrom_firstname,u.lastname AS userfrom_lastname,u.firstnamephonetic AS userfrom_firstnamephonetic,u.lastnamephonetic AS userfrom_lastnamephonetic,u.middlename AS userfrom_middlename,u.alternatename AS userfrom_alternatename,u.imagealt AS userfrom_imagealt,u.email AS userfrom_email,u.lastaccess AS userfrom_lastaccess, mub.id as userfrom_blocked, u2.id AS userto_id,u2.picture AS userto_picture,u2.firstname AS userto_firstname,u2.lastname AS userto_lastname,u2.firstnamephonetic AS userto_firstnamephonetic,u2.lastnamephonetic AS userto_lastnamephonetic,u2.middlename AS userto_middlename,u2.alternatename AS userto_alternatename,u2.imagealt AS userto_imagealt,u2.email AS userto_email,u2.lastaccess AS userto_lastaccess, mub2.id as userto_blocked -> FROM mdl_messages m -> INNER JOIN mdl_user u -> ON u.id = m.useridfrom -> INNER JOIN mdl_message_conversations mc -> ON mc.id = m.conversationid -> INNER JOIN mdl_message_conversation_members mcm -> ON mcm.conversationid = m.conversationid -> INNER JOIN mdl_user u2 -> ON u2.id = mcm.userid -> LEFT JOIN mdl_message_users_blocked mub -> ON (mub.blockeduserid = u.id AND mub.userid = '283702') -> LEFT JOIN mdl_message_users_blocked mub2 -> ON (mub2.blockeduserid = u2.id AND mub2.userid = '283702') -> LEFT JOIN mdl_message_user_actions mua -> ON (mua.messageid = m.id AND mua.userid = '283702' AND mua.action = '2') -> WHERE (m.useridfrom = '283702' OR mcm.userid = '283702') -> AND (m.useridfrom != mcm.userid OR mc.type = '3') -> AND u.deleted = 0 -> AND u2.deleted = 0 -> AND mua.id is NULL -> AND LOWER(smallmessage) LIKE LOWER('%world%') COLLATE utf8mb4_bin ESCAPE '\\' -> ORDER BY timecreated DESC LIMIT 0, 51\G *************************** 1. row *************************** uniqueid: 3195504_283702_283702 id: 3195504 useridfrom: 283702 useridto: 283702 subject: NULL fullmessage: Hello world 07/04/22 fullmessagehtml: fullmessageformat: 0 smallmessage: Hello world 07/04/22 conversationid: 646076 timecreated: 1649323998 isread: 0 userfrom_id: 283702 userfrom_picture: 0 userfrom_firstname: G*** userfrom_lastname: B******** userfrom_firstnamephonetic: userfrom_lastnamephonetic: userfrom_middlename: userfrom_alternatename: userfrom_imagealt: userfrom_email: user@example.com userfrom_lastaccess: 1649326941 userfrom_blocked: NULL userto_id: 283702 userto_picture: 0 userto_firstname: G*** userto_lastname: B******** userto_firstnamephonetic: userto_lastnamephonetic: userto_middlename: userto_alternatename: userto_imagealt: userto_email: user@example.com userto_lastaccess: 1649326941 userto_blocked: NULL 1 row in set (16.2572 sec) <<<<<<<<<<<<<<<<<< OLD VERSION MySQL SQL > SELECT CONCAT(m.id, '_', m.useridfrom, '_', mcm.userid) AS uniqueid, m.id, m.useridfrom, mcm.userid as useridto, m.subject, m.fullmessage, -> m.fullmessagehtml, m.fullmessageformat, m.smallmessage, m.conversationid, m.timecreated, 0 as isread, -> u.id AS userfrom_id,u.picture AS userfrom_picture,u.firstname AS userfrom_firstname,u.lastname AS userfrom_lastname,u.firstnamephonetic AS userfrom_firstnamephonetic,u.lastnamephonetic AS userfrom_lastnamephonetic,u.middlename AS userfrom_middlename,u.alternatename AS userfrom_alternatename,u.imagealt AS userfrom_imagealt,u.email AS userfrom_email,u.lastaccess AS userfrom_lastaccess, mub.id as userfrom_blocked, u2.id AS userto_id,u2.picture AS userto_picture,u2.firstname AS userto_firstname,u2.lastname AS userto_lastname,u2.firstnamephonetic AS userto_firstnamephonetic,u2.lastnamephonetic AS userto_lastnamephonetic,u2.middlename AS userto_middlename,u2.alternatename AS userto_alternatename,u2.imagealt AS userto_imagealt,u2.email AS userto_email,u2.lastaccess AS userto_lastaccess, mub2.id as userto_blocked -> FROM -> (SELECT m2.id AS 'id' -> FROM mdl_messages m2 -> WHERE m2.useridfrom = 283702 -> UNION -> SELECT m3.id AS 'id' -> FROM mdl_message_conversation_members mcm3 -> INNER JOIN mdl_messages m3 ON mcm3.conversationid = m3.conversationid -> WHERE mcm3.userid = 283702) der -> INNER JOIN mdl_messages m ON der.id = m.id -> INNER JOIN mdl_user u ON u.id = m.useridfrom -> INNER JOIN mdl_message_conversations mc ON mc.id = m.conversationid -> INNER JOIN mdl_message_conversation_members mcm ON mcm.conversationid = m.conversationid -> INNER JOIN mdl_user u2 ON u2.id = mcm.userid -> LEFT JOIN mdl_message_users_blocked mub ON (mub.blockeduserid = u.id AND mub.userid = '283702') -> LEFT JOIN mdl_message_users_blocked mub2 ON (mub2.blockeduserid = u2.id AND mub2.userid = '283702') -> LEFT JOIN mdl_message_user_actions mua ON (mua.messageid = m.id AND mua.userid = '283702' AND mua.action = '2') -> WHERE (m.useridfrom = '283702' OR mcm.userid = '283702') -> AND (m.useridfrom != mcm.userid OR mc.type = '3') -> AND u.deleted = 0 -> AND u2.deleted = 0 -> AND mua.id is NULL -> AND LOWER(m.smallmessage) LIKE LOWER('%world%') COLLATE utf8mb4_bin ESCAPE '\\' -> ORDER BY m.id DESC LIMIT 0, 51\G *************************** 1. row *************************** uniqueid: 3195504_283702_283702 id: 3195504 useridfrom: 283702 useridto: 283702 subject: NULL fullmessage: Hello world 07/04/22 fullmessagehtml: fullmessageformat: 0 smallmessage: Hello world 07/04/22 conversationid: 646076 timecreated: 1649323998 isread: 0 userfrom_id: 283702 userfrom_picture: 0 userfrom_firstname: G*** userfrom_lastname: B******** userfrom_firstnamephonetic: userfrom_lastnamephonetic: userfrom_middlename: userfrom_alternatename: userfrom_imagealt: userfrom_email: user@example.com userfrom_lastaccess: 1649326941 userfrom_blocked: NULL userto_id: 283702 userto_picture: 0 userto_firstname: G*** userto_lastname: B******** userto_firstnamephonetic: userto_lastnamephonetic: userto_middlename: userto_alternatename: userto_imagealt: userto_email: user@example.com userto_lastaccess: 1649326941 userto_blocked: NULL 1 row in set (0.0037 sec) <<<<<<<<<<<<<<<<<<<<<< NEW VERSION