How do I index for a mysql query using or with multiple columns

Question

My Query:

SELECT * FROM privatemessages69 
WHERE sender='19' OR recipient='19' 
ORDER BY id DESC;

My Table:

CREATE TABLE `privatemessages69` (
  `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 `msgpanel` (`sender`,`recipient`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50000 DEFAULT CHARSET=latin1

Explain:

mysql> explain SELECT * FROM privatemessages69 WHERE sender='19' OR recipient='19' ORDER BY id DESC;
+----+-------------+-------------------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table             | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-------------------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | SIMPLE      | privatemessages69 | index | msgpanel      | PRIMARY | 4       | NULL | 50191 | Using where |
+----+-------------+-------------------+-------+---------------+---------+---------+------+-------+-------------+
1 row in set (0.00 sec)

Can someone tell me the correct way of indexing this table for that query? If I remove the order by, type goes to ALL.

Thanks for any help

ypercubeᵀᴹ · Accepted Answer · 2011-04-27 07:27:46Z

3

Having one index on sender and one on recipient should be enough for such a query:

CREATE INDEX ind_sender
    ON privatemessages69 (sender) ;

CREATE INDEX ind_recipient 
    ON privatemessages69 (recipient) ;

And I don't think it's going to be faster if you write it using UNION.

You can also try adding these two indexes (and removing the above ones):

CREATE INDEX ind_sender_2
    ON privatemessages69 (sender, id) ;

CREATE INDEX ind_recipient_2
    ON privatemessages69 (recipient, id) ;

I can't test now but it may get rid of the filesort.

You can still edit your question and add at the end:

Time with ORDER BY: (3-4 seconds as you say)
Number of total rows in the table:
Number of rows with recipient=19:
Number of rows with sender=19:
Number of rows the query returns:

Some tests I did yesterday with about 300 thousand total rows showed the query running in less than a second. The slowness maybe due to configuration settings of MySQL.

Also: Does the query takes 3-4 seconds whatever you put (instead of 19)?

edited Apr 27, 2011 at 7:27

answered Apr 26, 2011 at 23:57

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

1 Comment

Brian Over a year ago

This definitely seems to be the fastest way of doing it. I guess the only real problem i'm having now is ORDER BY slowing down the query. Do you know of a good solution for speeding that up? (Should I ask this as a new question? New Here) Thank you for the help!

soulmerge · Accepted Answer · 2011-04-26 22:44:57Z

2

You can't index the table for that query, but here is an another one that will run fast, if you put 2 distinct indexes on sender and recipient:

SELECT * FROM (
    SELECT * FROM privatemessages69 WHERE sender=19
    UNION
    SELECT * FROM privatemessages69 WHERE recipient=19
) t
ORDER BY id DESC;

answered Apr 26, 2011 at 22:44

soulmerge

76.2k20 gold badges121 silver badges160 bronze badges

11 Comments

Kevin Peno Over a year ago

Any reference to why that is?

soulmerge Over a year ago

@Kevin Peno: Mysql cannot make use of an index spanning 2 two columns, if both columns are combined using OR. Have a look at the mysql documentation explaining index usage (last example box): dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Johan Over a year ago

@soulmerge, if you add a seperate index on recipient, then you can use the select * from privatemessages69 where sender = 19 or recipient = 19 and it will use and index on both. if you do not alter the table and thus do not add an index your code will not use an index on recipient because it is not the left-most part of a composite index, so how is your code better than using a simple OR ?

ypercubeᵀᴹ Over a year ago

@soulmerge: You are (or I should say MySQL's online documentation is) wrong in that point. Two (or more) indexes on two (or more) separate fields can be used with OR, exactly as Brian's query. The version i checked (5.0.51) shows type: index_merge in the EXPLAIN.

ypercubeᵀᴹ Over a year ago

This is a link to index-merge: dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html

|

Kevin Peno · Accepted Answer · 2011-04-26 23:02:25Z

1

I believe you would need to write an index where both columns have the first position. In your case adding:

CREATE INDEX `msgpanel_recp` ON `privatemessages69` (`recipient`,`id`)

edited Apr 26, 2011 at 23:02

answered Apr 26, 2011 at 22:47

Kevin Peno

9,2261 gold badge36 silver badges56 bronze badges

1 Comment

Brian Over a year ago

This is using type:all when ORDER BY is removed from the query

Collectives™ on Stack Overflow

How do I index for a mysql query using or with multiple columns

3 Answers 3

1 Comment

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related