CODEWITHSUNDEEP
mysql
Brian
Brian

Reputation: 137

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

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

Upvotes: 4

Views: 134

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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)?

Upvotes: 3

Kevin Peno
Kevin Peno

Reputation: 9196

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`)

Upvotes: 1

soulmerge
soulmerge

Reputation: 75714

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;

Upvotes: 2

Related Questions