Multiple indexing in a table mysql-CodePudding

I have a table structure like this

`CREATE TABLE `like_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `sender_id` int(11) NOT NULL,
 `receiver_id` int(11) NOT NULL,
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `index_user` (`sender_id`,`receiver_id`))`

I have indexed both sender_id and receiver_id. If I try to query this

`Select * from like_user where sender_id = 10`

The index works fine but on the other way around it doesn't.

`Select * from like_user where receiver_id = 11`

How can I make the index work on both the conditions.

The use case is that sender_id is the one who is liking a user and the person who sender id is liking is stored in receiver_id. So If sender wants to see all the users he likes, then indexing works, but if the receiver_id wants to see which senders have liked him, indexing stops working. how we can resolve it?

CodePudding user response：

Only prefix can be used. Postfix cannot. I think that two separate indices, one by sender and another by receiver, will be reasonable:

CREATE TABLE `like_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `sender_id` int(11) NOT NULL,
 `receiver_id` int(11) NOT NULL,
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY (`sender_id`),
 KEY (`receiver_id`)
 );

One of these indices will be used for each table copy. For example, for

SELECT *
FROM like_user t1
JOIN like_user t2 ON t1.sender_id = t2.receiver_id;

the first table copy (t1) will use KEY (`sender_id`) whereas another table copy will use KEY (`receiver_id`).