USING INDEX WITH "OR"

I’m trying to use index for the following query, but it resulted in filesort.

How should I change the query &/or index to improve performance (avoid filesort)?

I tried using UNION but filesort still exists.

CREATE TABLE message (  
  Ms_ID INT(16) UNSIGNED NOT NULL AUTO_INCREMENT, 
  conversation INT(16) UNSIGNED NOT NULL, 
  subject ENUM('E', 'F', 'S-C', 'S-A', 'R-RS', 'R-ILP', 'Re-M', 'Re-I', 'Re-R', 'O', 'SP', 'T', 'I', 'C', 'Of', 'R') DEFAULT NULL, 
  subject_ID INT(16) UNSIGNED DEFAULT NULL, 
  sender INT(11) UNSIGNED, -- NULL=guest
  recipient INT(11) UNSIGNED NOT NULL, 
  message VARCHAR(256), 
  status ENUM('U','R','SD', 'RD', 'SRD'), -- (unread, read, sender deleted, recipient deleted) 
  dateTime DATETIME NOT NULL,   
  PRIMARY KEY (Ms_ID), 
  FOREIGN KEY (sender) REFERENCES member (M_ID),
  FOREIGN KEY (recipient) REFERENCES member (M_ID)


CREATE INDEX xMs_D ON message (dateTime, conversation, recipient, sender, status, message)
CREATE INDEX xMs_R ON message (recipient, sender, status, dateTime, message)
CREATE INDEX xMs_S ON message (sender, recipient, status, dateTime, message)



EXPLAIN
  SELECT Ms.*, M.userName   
  FROM message Ms -- FORCE INDEX (xMs_S)
    INNER JOIN member M ON M_ID=IF (sender='3', recipient, sender)
  WHERE Ms.status!='SRD' AND ((sender='3' AND Ms.status!='SD') OR (recipient='3' AND Ms.status!='RD')) 
  GROUP BY conversation   
  ORDER BY dateTime DESC

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  Ms  ALL     xMs_S   NULL    NULL    NULL    77  Using where; Using temporary; Using filesort
1   SIMPLE  M   eq_ref  PRIMARY     PRIMARY     4   func    1   Using where

Leave a Reply

*

Hire Me
Follow Me!
Search
Most Popular Articles & Pages
Because your vote is Important
Sorry, there are no polls available at the moment.
Categories