MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix? 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

gives:
id   select_type   table   type   key                   key_length   ref                     rows   extra
1    SIMPLE        e1      ref    personId_categoryId   4            const                   59     Using index; Using temporary; Using filesort
1    SIMPLE        e2      ref    categoryId_personId   4            project.e1.categoryId   8      Using where; Using index

If I remove the GROUP BY I get &quot;Using index&quot;.
What's the problem here?  Is it something to do with joining a table onto itself?
(The indexes present on the table are the two shown in the explain output, containing the columns that the names suggest.)
DDL:
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
) ENGINE=InnoDB AUTO_INCREMENT=1465605 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Try to convert your query to
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


Update
It seems that MariaDB uses implicit STRAIGHT_JOIN... or plan builder cannot optimize the query correctly.
For query optimize it's enough to swap table copies. See fiddle for details.