MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `personId` int(10) unsigned NOT NULL,
  `categoryId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `personId_categoryId` (`personId`,`categoryId`),
  KEY `categoryId_personId` (`categoryId`,`personId`)  /* ,
  CONSTRAINT `entries_ibfk_1` FOREIGN KEY (`personId`) REFERENCES `people` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `entries_ibfk_2` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`id`) ON UPDATE CASCADE
*/ 
);
EXPLAIN
SELECT  e2.personId, COUNT(*)
   
FROM        entries e1
JOIN        entries e2   ON  e2.categoryId   =   e1.categoryId
                        AND  e2.personId    !=   e1.personId
   
WHERE       e1.personId  =  1
   
GROUP BY    e2.personId


id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
SIMPLE
e1
ref
personId_categoryId,categoryId_personId
personId_categoryId
4
const
1
Using index; Using temporary; Using filesort

1
SIMPLE
e2
ref
personId_categoryId,categoryId_personId
categoryId_personId
4
fiddle.e1.categoryId
1
Using where; Using index


EXPLAIN
SELECT  e2.personId, COUNT(*)
   
FROM        entries e2
JOIN        entries e1   ON  e2.categoryId   =   e1.categoryId
                        AND  e2.personId    !=   e1.personId
   
WHERE       e1.personId  =  1
   
GROUP BY    e2.personId


id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
SIMPLE
e2
range
personId_categoryId,categoryId_personId
personId_categoryId
4
null
1
Using where; Using index

1
SIMPLE
e1
ref
personId_categoryId,categoryId_personId
personId_categoryId
8
const,fiddle.e2.categoryId
1
Using index


EXPLAIN
SELECT e2.personId, COUNT(*) - SUM(e2.personId = e1.personId)
FROM entries e1
JOIN entries e2 USING (categoryId)
WHERE e1.personId  =  1
GROUP BY e2.personId


id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
SIMPLE
e1
ref
personId_categoryId,categoryId_personId
personId_categoryId
4
const
1
Using index; Using temporary; Using filesort

1
SIMPLE
e2
ref
categoryId_personId
categoryId_personId
4
fiddle.e1.categoryId
1
Using index


EXPLAIN
SELECT e2.personId, COUNT(*) - SUM(e2.personId = e1.personId)
FROM entries e2
JOIN entries e1 USING (categoryId)
WHERE e1.personId  =  1
GROUP BY e2.personId


id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
SIMPLE
e2
index
categoryId_personId
personId_categoryId
8
null
1
Using index

1
SIMPLE
e1
ref
personId_categoryId,categoryId_personId
personId_categoryId
8
const,fiddle.e2.categoryId
1
Using index

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	e1	ref	personId_categoryId,categoryId_personId	personId_categoryId	4	const	1	Using index; Using temporary; Using filesort
1	SIMPLE	e2	ref	personId_categoryId,categoryId_personId	categoryId_personId	4	fiddle.e1.categoryId	1	Using where; Using index

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	e2	range	personId_categoryId,categoryId_personId	personId_categoryId	4	null	1	Using where; Using index
1	SIMPLE	e1	ref	personId_categoryId,categoryId_personId	personId_categoryId	8	const,fiddle.e2.categoryId	1	Using index

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	e2	index	categoryId_personId	personId_categoryId	8	null	1	Using index
1	SIMPLE	e1	ref	personId_categoryId,categoryId_personId	personId_categoryId	8	const,fiddle.e2.categoryId	1	Using index