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
Last activity: May 4, 2025, 05:07 AM