mysql many tables, multiple columns group by-CodePudding

The table I have configured is as follows.

TABLE CAT{
  ID number primary key,
  CATNAME varchar2,
  ...
}

<Many-to-Many>
TABLE CAT_CHARR{
  CATID number,
  CHARRID number
}

TABLE CHARR{
  ID number,
  CHARRNAME varchar2,
  SPECIES varchar2,
 ...
}

cats have its own characteristics, and those characteristics were considered characteristics unique to that species.


INSERT INTO CHARR(CHARRNAME, SPECIES) VALUES ( 'BLUE FUR', 'russianblue');
INSERT INTO CHARR(CHARRNAME, SPECIES) VALUES ( 'NO FUR', 'sphinx');
...

Duplication is possible as follows.

INSERT INTO CAT_CHARR(CATID, CHARRID) VALUES (1, 1);
INSERT INTO CAT_CHARR(CATID, CHARRID) VALUES (1, 1);
INSERT INTO CAT_CHARR(CATID, CHARRID) VALUES (1, 2);
INSERT INTO CAT_CHARR(CATID, CHARRID) VALUES (1, 3);

I want to calculate only the highest trait of each cat's species.

SELECT CAT.ID, CHARR.SPECIES, CHARR.CHARRNAME, COUNT(*) AS CNT
FROM CAT
INNER CAT_CHARR ON CAT.ID = CAT_CHARR.CATID
INNER JOIN CHARR ON CHARR.ID = CAT_CHARR.CHARRID
GROUP BY CAT.ID, CHARR.SPECIES, CHARR.CHARRNAME

If there is no group by, the speed is fast. But if there are group by and count(*), the speed is too slow.

I used google translate, so the explanation might be a little weird. If you write a comment, I'll try harder to translate. Sorry

CodePudding user response：

INNER JOIN costly operation you can replace this. And you get something like this

SELECT CAT.ID, CHARR.SPECIES, CHARR.CHARRNAME, COUNT(*) AS CNT
FROM CAT, CAT_CHARR, CHARR
WHERE CAT.ID = CAT_CHARR.CATID AND CHARR.ID = CAT_CHARR.CHARRID AND CHARR.ID = CAT.ID
GROUP BY CAT.ID, CHARR.SPECIES, CHARR.CHARRNAME

CodePudding user response：

For performance the many-to-many table needs

PRIMARY KEY(catid, carrid),
INDEX(carrid, catid)

(or vice versa)