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   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 "Using index".

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


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;
  • Show table's DDL.
    – Akina
    Jul 10, 2020 at 8:08
  • Added to the question now @Akina
    – Codemonkey
    Jul 10, 2020 at 8:20
  • categoryId_personId index exists. So everything is OK. Why is this still using temporary and filesort - unequiation condition does not allow to use index for grouping. Moreover, "using index" in a query without grouping means that the index is used as covering (as compact version of the table), I think.
    – Akina
    Jul 10, 2020 at 8:25
  • I've tried removing the != condition (I could just filter it out in PHP afterwards) but it made no difference to the EXPLAIN output.
    – Codemonkey
    Jul 10, 2020 at 9:40
  • (If that's what you meant by "unequiation")
    – Codemonkey
    Jul 10, 2020 at 9:41

1 Answer 1


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


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.

  • Three times slower and still Using index; Using temporary; Using filesort. (Additionally to make them equal I had to add in AND e2.personId != 1, but the results were largely the same either way.
    – Codemonkey
    Jul 10, 2020 at 9:38
  • @Codemonkey The answer updated - check.
    – Akina
    Jul 10, 2020 at 9:48
  • Interesting - not used dbfiddle before, looks like a useful tool. Sadly even with the bottom option I still get temp/filesort when using it on this machine. I will investigate more later, thank you.
    – Codemonkey
    Jul 10, 2020 at 9:53
  • @Codemonkey Execution plan depends on table data and current statistic primarily - try to perform ANALYZE TABLE or even OPTIMIZE TABLE - maybe it will give some effect...
    – Akina
    Jul 10, 2020 at 9:58
  • I'm out at the moment but will try those when I get home. The data has high cardinality so it should be good for an index, from what little I understand!
    – Codemonkey
    Jul 10, 2020 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.