Sample Header Ad - 728x90

MariaDB: Why is this still using temporary and filesort - is it because I'm using a self-join? How to fix?

0 votes
1 answer
416 views
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;
Asked by Codemonkey (265 rep)
Jul 10, 2020, 07:48 AM
Last activity: May 4, 2025, 05:07 AM