EXPLAIN SELECT SENDER AS PROFILEID, MESSAGE, 'R' AS SR, SEEN, DATE
FROM `MESSAGE_LOG`
JOIN MESSAGES ON ( MESSAGE_LOG.ID = MESSAGES.ID )
WHERE `RECEIVER` = '9063911' AND SENDER NOT
IN ('658', '87238', '99359', '643848', '651922', '734783', '880643'
) AND `TYPE` = 'R' AND `IS_MSG` = 'Y'
UNION SELECT RECEIVER AS PROFILEID, MESSAGE, 'S' AS SR, SEEN, DATE
FROM `MESSAGE_LOG`
JOIN MESSAGES ON ( MESSAGE_LOG.ID = MESSAGES.ID )
WHERE `SENDER` = '9063911' AND RECEIVER NOT
IN (
'658', '87238', '99359', '643848', '651922', '734783', '880643'
) AND `TYPE` = 'R' AND `IS_MSG` = 'Y'
ORDER BY DATE DESC
Explain query returns:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY MESSAGE_LOG ref ID,SENDER,RECEIVER RECEIVER 4 const 142 Using where
1 PRIMARY MESSAGES eq_ref ID ID 4 newjs.MESSAGE_LOG.ID 1
2 UNION MESSAGE_LOG range ID,SENDER,RECEIVER SENDER 8 NULL 168 Using where
2 UNION MESSAGES eq_ref ID ID 4 newjs.MESSAGE_LOG.ID 1
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL
The query is taking around 15 sec to execute. What could be the reason and how the query can be optimized further.
EDIT: Is it because mysql is not able to utilize indexes properly?
Indexes on MESSAGE_LOG table:
Keyname Type Cardinality Action Field
ID UNIQUE 44491833 Edit Drop ID
SENDER INDEX 44491833 Edit Drop SENDER,RECEIVER
RECEIVER INDEX 1483061 Edit Drop RECEIVER,FOLDERID,OBSCENE
Indexes on MESSAGES table:
Keyname Type Cardinality Action Field
ID UNIQUE 43572638 Edit Drop ID
Can I hint mysql to use right index? If yes, then how and on which column?
Tables structure:
CREATE TABLE `MESSAGE_LOG` (
`SENDER` int(8) unsigned NOT NULL DEFAULT '0',
`RECEIVER` int(8) unsigned NOT NULL DEFAULT '0',
`DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`IP` int(10) unsigned DEFAULT NULL,
`RECEIVER_STATUS` char(1) NOT NULL DEFAULT 'U',
`FOLDERID` mediumint(9) NOT NULL DEFAULT '0',
`MSG_OBS_ID` int(11) NOT NULL DEFAULT '0',
`SENDER_STATUS` char(1) NOT NULL DEFAULT 'U',
`TYPE` char(1) NOT NULL DEFAULT 'R',
`ID` int(11) NOT NULL,
`OBSCENE` char(1) NOT NULL DEFAULT 'N',
`IS_MSG` char(1) NOT NULL DEFAULT 'N',
`SEEN` char(1) NOT NULL,
UNIQUE KEY `ID` (`ID`),
KEY `SENDER` (`SENDER`,`RECEIVER`),
KEY `RECEIVER` (`RECEIVER`,`FOLDERID`,`OBSCENE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `MESSAGES` (
`ID` int(11) NOT NULL,
`MESSAGE` text NOT NULL,
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1