Hint indexes to mysql on Join

85 views Asked by At
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
0

There are 0 answers