0

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

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;
  • Show table's DDL. – Akina Jul 10 at 8:08
  • Added to the question now @Akina – Codemonkey Jul 10 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 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 at 9:40
  • (If that's what you meant by "unequiation") – Codemonkey Jul 10 at 9:41
0

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.

| improve this answer | |
  • 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 at 9:38
  • @Codemonkey The answer updated - check. – Akina Jul 10 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 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 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 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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