MySQL Query slowed down when using OR operator

Question

As my private message database has begun to grow, i'm noticing some considerable slow downs on the following query.

The Query:

SELECT * FROM privatemessages WHERE sender='940' OR recipient='940' ORDER BY id DESC LIMIT 1000;

(The 940 can be any userid)

The Table:

   CREATE TABLE `privatemessages` (
  `id` int(11) NOT NULL auto_increment,
  `recipient` int(11) NOT NULL,
  `sender` int(11) NOT NULL,
  `time` int(11) NOT NULL,
  `readstatus` int(11) NOT NULL,
  `message` varchar(255) NOT NULL,
  `messagetype` int(11) NOT NULL,
  `rdeleted` int(11) NOT NULL,
  `sdeleted` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `recipient` (`recipient`),
  KEY `sender` (`sender`),
  KEY `read` (`readstatus`),
  KEY `time` (`time`),
  KEY `openmessagingpanel` (`recipient`,`readstatus`),
  KEY `openpmthreadrev` (`recipient`,`sender`),
  KEY `openpmthread` (`sender`,`recipient`)
) ENGINE=InnoDB AUTO_INCREMENT=8650153 DEFAULT CHARSET=latin1

MySQL Explain:

+----+-------------+-----------------+-------------+------------------------------------------------------------------+------------------+---------+------+-------+--------------------------------------------+
| id | select_type | table           | type        | possible_keys                                                    | key              | key_len | ref  | rows  | Extra                                      |
+----+-------------+-----------------+-------------+------------------------------------------------------------------+------------------+---------+------+-------+--------------------------------------------+
|  1 | SIMPLE      | privatemessages | index_merge | recipient,sender,openmessagingpanel,openpmthreadrev,openpmthread | sender,recipient | 4,4     | NULL | 26100 | Using union(sender,recipient); Using where |
+----+-------------+-----------------+-------------+------------------------------------------------------------------+------------------+---------+------+-------+--------------------------------------------+
1 row in set (0.00 sec)

Does anyone know what I need to do to get this query back up to speed? There is roughly 8 million records.

Thank you.

Do you really need to fetch all the fields for the rows you found (SELECT *) ? — Cristian Boariu
– Cristian Boariu, Commented Apr 26, 2011 at 7:38
Have you defined another index on this table that is combined for sender and recipient? When I just take your table definition I get a much cleaner explain plan: — Wes
– Wes, Commented Apr 26, 2011 at 7:39
Sorry, I had messed up my copy and pastes. The original post has been corrected with how the database is now, although it is pretty messed up from testing some things out earlier today. — Brian
– Brian, Commented Apr 26, 2011 at 7:53

trickwallett · Accepted Answer · 2011-04-26 08:00:31Z

0

2 thoughts:

Is it worth creating a view for the messages with undeleted status, and selecting the messages from there, on the assumption there'll be less recs to deal with.
Being verbose about SELECT-ing the fields you need usually yields a performance gain

answered Apr 26, 2011 at 8:00

trickwallett

2,45817 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

helle · Accepted Answer · 2011-04-26 08:03:35Z

0

You allready have an Index on the sender and receiver column.

Assuming you need the request for the GUI where the user can select a message to read:

If you would only select id, title and maybe the time this would be enough for displaying the links to the messages. as well you can set limit to 50 or so and do some pageination.

or do you need it for some kind of export? then you should rather use the export functionality of mysql ...

hope this helps you.

answered Apr 26, 2011 at 8:03

helle

11.8k9 gold badges58 silver badges86 bronze badges

Collectives™ on Stack Overflow

MySQL Query slowed down when using OR operator

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related