I have a simple invitation table:
CREATE TABLE `invitation` (
`invitation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`inviter_id` int(10) unsigned NOT NULL,
`invitee_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`invitation_id`),
UNIQUE KEY `invitee_inviter_idx` (`invitee_id`,`inviter_id`)
)
I want to select an invitation by inviter 70 to invitee 62 and vice versa:
EXPLAIN SELECT * FROM `invitation` WHERE
(invitee_id = 70 AND inviter_id = 62) OR (invitee_id = 62 AND inviter_id = 70)
But this query is of type ALL and doesn't use the invitee_inviter_idx. Please tell me what is wrong here ?
Thank you!
==EDIT== Sorry, i was wrong about the schema, it has one more field: request_ts. This time the query plan is ALL.
CREATE TABLE `invitation` (
`invitation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`inviter_id` int(10) unsigned NOT NULL,
`invitee_id` int(10) unsigned NOT NULL,
`request_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`invitation_id`),
UNIQUE KEY `invitee_inviter_idx` (`invitee_id`,`inviter_id`)
)
Here is my exlain result:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE invitation ALL invitee_inviter_idx \N \N \N 1 Using where
You just need to get enough rows into the table. MySQL will do a full table scan on small tables simply because it's cheap enough.
My example puts 65k rows into the table and it will use the index.