[MySQL] SELECT query met JOIN en GROUP BY traag

[-Paul-]

Ik loop tegen het probleem aan dat een query in MySQL waarbij een JOIN en vervolgens een GROUP BY wordt gedaan erg traag is. De losse JOIN zonder GROUP BY en de GROUP BY zonder JOIN gaan razendsnel. De SELECT query zoals hij hieronder staat doet er iets meer dan 2 seconden over. Zonder de JOIN of zonder de GROUP BY minder dan 0,004 seconden. Waarom is de combinatie dan zo traag? Is het nog mogelijk dit substantieel te versnellen?

SQL:

SELECT t_bpk.p
FROM t_bpk
JOIN t_bp ON t_bpk.bp = t_bp.id
GROUP BY t_bpk.p
ORDER BY NULL;

SQL:

CREATE TABLE t_bp (id INT PRIMARY KEY);
INSERT INTO t_bp (SELECT n FROM generator_1m WHERE n < 150000);

CREATE TABLE t_bpk (p INT, bp INT);
CREATE INDEX p ON t_bpk (p);
CREATE INDEX bp ON t_bpk (bp);
INSERT INTO t_bpk (SELECT n,n FROM generator_1m WHERE n < 150000);

Beide tabellen heb ik gevuld met dummy data met behulp van deze generator: http://use-the-index-luke...07-30/mysql-row-generator

[ Voor 6% gewijzigd door -Paul- op 13-10-2015 12:39 ]

[Guillome]

1. gebruik echt goede namen. Dit is abacadabra. Ook voor jou als je na 2 jaar weer in je project duikt.
2. traagheid komt door de order by null. Waarom heb je die? Zonder die is de query razendsnel

[-Paul-]

Dank voor de reactie. Sorry voor de abacadabra. Ik had de order by null van: http://dev.housetrip.com/2013/04/19/mysql-order-by-null/

Het gaat bij mij echter helemaal niet sneller zonder de order by null:

SQL:

13:46:43    SELECT t_bpk.p  FROM t_bpk JOIN t_bp ON t_bpk.bp = t_bp.id  GROUP BY t_bpk.p    1000000 row(s) returned 4,892 sec / 0,186 sec

Na enkele keren uitvoeren gaat het overigens plots wel snel, ik denk doordat er iets gecached wordt.

[ Voor 55% gewijzigd door -Paul- op 13-10-2015 13:47 ]

[Guillome]

Dan doe je mogelijk toch iets fout. Heb precies gedaan wat jij gedaan hebt:

[ Voor 57% gewijzigd door Guillome op 13-10-2015 14:09 ]

[-Paul-]

Ik kan de afbeelding niet zien. Wat gebeurt er als je de tabel t_bpk met de volgende data vult (voor het veld p nu niet n, maar n/100, zodat er daadwerkelijk gegroepeerd moet worden):

SQL:

CREATE TABLE t_bpk (p INT, bp INT);
CREATE INDEX p ON t_bpk (p);
CREATE INDEX bp ON t_bpk (bp);
CREATE INDEX pbp ON t_bpk (p,bp);
INSERT INTO t_bpk (SELECT n/100,n FROM generator_1m WHERE n < 1000000);

[regtur2]

Kun je niet distinct gebruiken en group by eruit gooien?

code:

SELECT DISTINCT t_bpk.p FROM t_bpk JOIN t_bp ON t_bpk.bp = t_bp.id ORDER BY NULL

[-Paul-]

regtur2 schreef op dinsdag 13 oktober 2015 @ 19:03:
Kun je niet distinct gebruiken en group by eruit gooien?

In dit geval wel, maar uiteindelijk komen er meer kolommen in de tabel waarover ik wil sommeren.

[Guillome]

Dat schiet ook niet op, daarbij is distinct voor een ander doel en groupby precies voor dit doel.

[Guillome]

moment

[ Voor 99% gewijzigd door Guillome op 14-10-2015 09:19 ]

[Guillome]

Oeps: moest een edit zijn.

Sowieso kloppen je tabellen niet helemaal.
Maak je 2e tabel:
p int PRIMARY key auto inc not nullable
pb not nullable

Tabel 1 ook primary key maken en not nullable int

Insert dit:

SQL:

INSERT INTO t_bpk (bp) (SELECT round(n / 100) + 1 FROM generator_1m WHERE n < 1000000);

Daarna is jouw query weer iets sneller.
1 sec. Maar 1 sec is logisch als je 1m rijen wilt groeperen in 1 miljoen groepen

Maar goed, maak eerst eens een realistische case. Je wilt nu group by op een primary key die je select. Waarom? Dat is nutteloos.

[ Voor 28% gewijzigd door Guillome op 14-10-2015 09:37 ]

[-Paul-]

Je hebt gelijk, ik heb de zaak zoals hij moet zijn erg versimpeld in de hoop dat het duidelijker zou worden, maar het tegendeel blijkt het geval.

Is die round noodzakelijk? Zonder werkt het volgens mij ook gewoon?

Hieronder iets dat meer richting de uiteindelijke tabel gaat, met jouw opmerkingen verwerkt. Voor 150.000 rows duurt het nog steeds bijna 0,2 sec. Dit lijkt me nog steeds erg veel, gezien de snelheid als ik ofwel de JOIN ofwel de GROUP BY eruit haal. Waarom moet de combinatie zoveel langer duren? Is hier echt niks aan te doen?

SQL:

CREATE TABLE t_bestelling_product (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, bestelling INT NOT NULL, aantal INT NOT NULL);
INSERT INTO t_bestelling_product (bestelling, aantal) (SELECT n,ROUND(n/15000)+1 FROM generator_1m WHERE n < 150000);

CREATE TABLE t_bestelling_product_koppeling (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, product INT NOT NULL, bestelling_product INT NOT NULL, verhouding INT NOT NULL);
CREATE INDEX product ON t_bestelling_product_koppeling (product);
INSERT INTO t_bestelling_product_koppeling (product, bestelling_product, verhouding) (SELECT ROUND(n/100)+1, n, ROUND(n/15000)+1 FROM generator_1m WHERE n < 150000);

SQL:

SELECT bpk.product
FROM t_bestelling_product_koppeling bpk
JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id
GROUP BY bpk.product

[Guillome]

Tja, als je een resultset hebt van 150.000 records.. waarom niet een beperkte resultset?
Ik denk niet dat 0.2 sec langzaam is voor zo`n resultaatset, maar ik had m ook sneller verwacht.

Overigens: je index klopt niet. Heb je op de verkeerder kolom gedaan

SQL:

CREATE INDEX product ON t_bestelling_product_koppeling (product);

vs

SQL:

JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id

[ Voor 51% gewijzigd door Guillome op 14-10-2015 11:53 ]

[-Paul-]

Hm. Ik was in de veronderstelling dat die index door de GROUP BY werd gebruikt. Klopt dit niet? Het toevoegen van een index op bestelling_product_koppeling.bestelling_product maakt het ook niet sneller.

[Guillome]

Er moet sowieso een index op kolommen komen waarop je group-by`t, joint of where`t

[ Voor 11% gewijzigd door Guillome op 14-10-2015 13:17 ]

[-Paul-]

Ik snap nog steeds niet waarom het zo langzaam gaat. Als ik alleen een JOIN doe dan gaat het razendsnel:

SQL:

12:46:20    SELECT bpk.product FROM t_bestelling_product_koppeling bpk JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id   149999 row(s) returned  0,0031 sec / 0,214 sec

Ook de losse GROUP BY (op 150000 rows) gaat lekker vlot.

SQL:

12:46:04    SELECT bpk.product FROM t_bestelling_product_koppeling bpk  GROUP BY bpk.product    1501 row(s) returned    0,036 sec / 0,000091 sec

Dus de operaties op zich zijn het probleem niet. Maar waarom de combinatie dan wel?

[Juup]

Kun je een "explain" laten zien van de langzame query?

[Merethil]

-Paul- schreef op donderdag 15 oktober 2015 @ 12:46:
Ik snap nog steeds niet waarom het zo langzaam gaat. Als ik alleen een JOIN doe dan gaat het razendsnel:

SQL:

12:46:20    SELECT bpk.product FROM t_bestelling_product_koppeling bpk JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id   149999 row(s) returned  0,0031 sec / 0,214 sec

Ook de losse GROUP BY (op 150000 rows) gaat lekker vlot.

SQL:

12:46:04    SELECT bpk.product FROM t_bestelling_product_koppeling bpk  GROUP BY bpk.product    1501 row(s) returned    0,036 sec / 0,000091 sec

Dus de operaties op zich zijn het probleem niet. Maar waarom de combinatie dan wel?

Wat komt er uit een explain bij de verschillende stappen? MySQL kan je heel mooi aangeven wat er gebeurt in de specifieke query, dus EXPLAIN ze alledrie even en kijk waarom die met join én group by zo traag is vergeleken met de rest.

[-Paul-]

SQL:

mysql> EXPLAIN SELECT bpk.product FROM t_bestelling_product_koppeling bpk JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id;
+----+-------------+-------+--------+--------------------+---------+---------+-----------------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys      | key     | key_len | ref                                     | rows   | Extra       |
+----+-------------+-------+--------+--------------------+---------+---------+-----------------------------------------+--------+-------------+
|  1 | SIMPLE      | bpk   | ALL    | bestelling_product | NULL    | NULL    | NULL                                    | 150040 | NULL        |
|  1 | SIMPLE      | bp    | eq_ref | PRIMARY            | PRIMARY | 4       | kievitam_default.bpk.bestelling_product |      1 | Using index |
+----+-------------+-------+--------+--------------------+---------+---------+-----------------------------------------+--------+-------------+
2 rows in set (0,00 sec)

SQL:

mysql> EXPLAIN SELECT bpk.product FROM t_bestelling_product_koppeling bpk GROUP BY bpk.product;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | bpk   | index | product       | product | 4       | NULL | 150040 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
1 row in set (0,00 sec)

SQL:

mysql> EXPLAIN SELECT bpk.product FROM t_bestelling_product_koppeling bpk JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id GROUP BY bpk.product;
+----+-------------+-------+--------+----------------------------+---------+---------+-----------------------------------------+--------+---------------------------------+
| id | select_type | table | type   | possible_keys              | key     | key_len | ref                                     | rows   | Extra                           |
+----+-------------+-------+--------+----------------------------+---------+---------+-----------------------------------------+--------+---------------------------------+
|  1 | SIMPLE      | bpk   | ALL    | product,bestelling_product | NULL    | NULL    | NULL                                    | 150040 | Using temporary; Using filesort |
|  1 | SIMPLE      | bp    | eq_ref | PRIMARY                    | PRIMARY | 4       | kievitam_default.bpk.bestelling_product |      1 | Using index                     |
+----+-------------+-------+--------+----------------------------+---------+---------+-----------------------------------------+--------+---------------------------------+
2 rows in set (0,00 sec)

[Juup]

Using temporary; Using filesort

Dat is het probleem. Fix dat.

[-Paul-]

Gefixed, maar het gaat nu alleen maar langzamer.

SQL:

mysql> EXPLAIN SELECT bpk.product FROM t_bestelling_product_koppeling bpk FORCE INDEX (product) JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id GROUP BY bpk.product;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                                     | rows   | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------------+--------+-------------+
|  1 | SIMPLE      | bpk   | index  | product       | product | 4       | NULL                                    | 150040 | NULL        |
|  1 | SIMPLE      | bp    | eq_ref | PRIMARY       | PRIMARY | 4       | kievitam_default.bpk.bestelling_product |      1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------------------+--------+-------------+
2 rows in set (0,01 sec)

SQL:

13:28:53    SELECT bpk.product FROM t_bestelling_product_koppeling bpk FORCE INDEX (product) JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id GROUP BY bpk.product    1501 row(s) returned    0,293 sec / 0,00011 sec

Edit: op andere manier gefixed, foreign key toegevoegd. Het is nu niet meer langzamer, maar ook niet aanmerkelijk sneller.

SQL:

DROP TABLE IF EXISTS t_bestelling_product_koppeling;
DROP TABLE IF EXISTS t_bestelling_product;

CREATE TABLE t_bestelling_product (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    bestelling INT NOT NULL,
    aantal INT NOT NULL
);
INSERT INTO t_bestelling_product (bestelling, aantal) (SELECT n+1,ROUND(n/15000)+1 FROM generator_1m WHERE n < 150000);

CREATE TABLE t_bestelling_product_koppeling (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    product INT NOT NULL,
    bestelling_product INT NOT NULL,
    verhouding INT NOT NULL,
    INDEX product (product,bestelling_product),
    INDEX bestelling_product (bestelling_product,product),
    FOREIGN KEY (bestelling_product) REFERENCES t_bestelling_product (id) ON DELETE CASCADE
);
INSERT INTO t_bestelling_product_koppeling (product, bestelling_product, verhouding) (SELECT ROUND(n/100)+1, n+1, ROUND(n/15000)+1 FROM generator_1m WHERE n < 150000);

SQL:

18:05:59    SELECT bpk.product FROM t_bestelling_product_koppeling bpk JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id GROUP BY bpk.product  1501 row(s) returned    0,175 sec / 0,000091 sec

SQL:

mysql> EXPLAIN SELECT bpk.product FROM t_bestelling_product_koppeling bpk JOIN t_bestelling_product bp ON bpk.bestelling_product = bp.id GROUP BY bpk.product;
+----+-------------+-------+--------+-------------------------------+---------+---------+-----------------------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys                 | key     | key_len | ref                                     | rows   | Extra       |
+----+-------------+-------+--------+-------------------------------+---------+---------+-----------------------------------------+--------+-------------+
|  1 | SIMPLE      | bpk   | index  | product,bestelling_product,id | product | 8       | NULL                                    | 150040 | Using index |
|  1 | SIMPLE      | bp    | eq_ref | PRIMARY                       | PRIMARY | 4       | kievitam_default.bpk.bestelling_product |      1 | Using index |
+----+-------------+-------+--------+-------------------------------+---------+---------+-----------------------------------------+--------+-------------+
2 rows in set (0,00 sec)

[ Voor 47% gewijzigd door -Paul- op 15-10-2015 18:06 ]