MySQL Query slowed down when using OR operator

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,
开发者_如何学JAVA  `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.

Queries with ORs in them are, unfortunately, slow because they cannot take proper advantage of indexing. There is, however, a solution. It is possible to express a cheaper yet functionally equivalent request to the database. You can optimize query performance by replacing the ORs with UNION-ed queries instead, with all of the UNION-ed queries optimally indexable.

You need to transform your query as follows. It works better with CTEs, but you could easily rewrite it with sub-queries or temporary tables instead if the version of MySQL you are using doesn't have CTEs available.

WITH
    cts AS
    (
        SELECT *
        FROM privatemessages
        WHERE sender='940'
        ORDER BY id DESC
        LIMIT 1000
    ),
    ctr AS
    (
        SELECT *
        FROM privatemessages
        WHERE recipient='940'
        ORDER BY id DESC
        LIMIT 1000
    )
SELECT * FROM cts
UNION
SELECT * FROM ctr
ORDER BY id desc
LIMIT 1000;

Each of these CTE sub-queries will run optimally indexed and stop after the first 1000 rows are found. You then combine the two and throw away half of the results based on id ordering. Fetching 2,000 rows to return 1,000 is still going to be 13x faster than what your EXPLAIN shows (scanning 26,100 rows).

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

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.