expertenaustausch > comp.* > comp.datenbanken.mysql

Stefan Ram (27.08.2018, 21:51)
Ich mache ein JOIN einer Fremdschlüsselspalte mit einer
Primärschlüsselspalte. Beide sollten ein Register (KEY,
INDEX) haben. Laut EXPLAIN könnte die Abfrage das Register
PRIMARY auch nutzen (Spalte "possible_keys"), tut es aber
nicht (Spalte "key"). Warum nicht?

PS: Verwendung von "INTEGER" statt "VARCHAR ( 255 )" ändert
das nicht.

PPS: Hat der JOIN-Buffer das Register überflüssig gemacht?
Aber auch wenn man das erste SELECT entfernt, kommt wieder
die gleiche Ausgabe.

WARNUNG: Skript LOESCHT Schema "S"!

Eingabe

\W SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ) );

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '2', 'Amelie Schulz' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '3', 'Jonas Seidel' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '4', 'Mika Kuhn' );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
TEXT VARCHAR ( 255 ),
FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ));

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin bevorzugt blaue Moebel.' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin wuenscht keine Anrufe' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '4', 'Kunde wird im Juni umziehen.' );

SELECT NAME, TEXT FROM KUNDE INNER JOIN NOTIZ ON KUNDE.KUNDE = NOTIZ.KUNDE;

EXPLAIN SELECT NAME, TEXT FROM KUNDE INNER JOIN NOTIZ ON KUNDE.KUNDE = NOTIZ.KUNDE;

Ausgabe (gekürzt)

+----------------+----------------------------------------+
| NAME | TEXT |
+----------------+----------------------------------------+
| Helene Schmidt | Kundin beschwert sich ueber Lieferung. |
| Helene Schmidt | Kundin bevorzugt blaue Moebel. |
| Helene Schmidt | Kundin wuenscht keine Anrufe |
| Mika Kuhn | Kunde wird im Juni umziehen. |
+----------------+----------------------------------------+

+----+-------+---------------+------+----------------------------------------------------+
| id | table | possible_keys | key | Extra |
+----+-------+---------------+------+----------------------------------------------------+
| 1 | KUNDE | PRIMARY | NULL | NULL |
| 1 | NOTIZ | KUNDE | NULL | Using where; Using join buffer (Block Nested Loop) |
+----+-------+---------------+------+----------------------------------------------------+
Stefan Ram (27.08.2018, 22:12)
ram (Stefan Ram) writes:
>INDEX) haben. Laut EXPLAIN könnte die Abfrage das Register
>PRIMARY auch nutzen (Spalte "possible_keys"), tut es aber
>nicht (Spalte "key"). Warum nicht?


Könnte das die Erklärung sein:

|For InnoDB, a secondary index might cover the selected
|columns even if the query also selects the primary key
|because InnoDB stores the primary key value with each
|secondary index.
Stefan Ram (27.08.2018, 22:21)
ram (Stefan Ram) writes:
>ram (Stefan Ram) writes:
>Könnte das die Erklärung sein:
>|For InnoDB, a secondary index might cover the selected
>|columns even if the query also selects the primary key
>|because InnoDB stores the primary key value with each
>|secondary index.


Wenn ich das Register NAME jedoch entferne, dann wird
angeblich gar kein Register mehr verwendet. Warum nicht?

main.sql

\W SET sql_mode = 'ANSI,TRADITIONAL';
DROP SCHEMA S; CREATE SCHEMA S; USE S;
CREATE TABLE KUNDE ( KUNDE VARCHAR ( 255 ) PRIMARY KEY, NAME VARCHAR ( 255 ) );

INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '1', 'Helene Schmidt' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '2', 'Amelie Schulz' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '3', 'Jonas Seidel' );
INSERT INTO KUNDE ( KUNDE, NAME ) VALUES ( '4', 'Mika Kuhn' );

CREATE TABLE NOTIZ
( KUNDE VARCHAR ( 255 ),
TEXT VARCHAR ( 255 ),
FOREIGN KEY ( KUNDE ) REFERENCES KUNDE ( KUNDE ));

INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin beschwert sich ueber Lieferung.' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin bevorzugt blaue Moebel.' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '1', 'Kundin wuenscht keine Anrufe' );
INSERT INTO NOTIZ ( KUNDE, TEXT ) VALUES ( '4', 'Kunde wird im Juni umziehen.' );

EXPLAIN SELECT NAME, TEXT FROM KUNDE INNER JOIN NOTIZ ON KUNDE.KUNDE = NOTIZ.KUNDE;

Protokoll (gekürzt)

+----+-------+---------------+------+----------------------------------------------------+
| id | table | possible_keys | key | Extra |
+----+-------+---------------+------+----------------------------------------------------+
| 1 | KUNDE | PRIMARY | NULL | NULL |
| 1 | NOTIZ | KUNDE | NULL | Using where; Using join buffer (Block Nested Loop) |
+----+-------+---------------+------+----------------------------------------------------+
Axel Schwenke (28.08.2018, 15:16)
On 27.08.2018 21:51, Stefan Ram wrote:
> Ich mache ein JOIN einer Fremdschlüsselspalte mit einer
> Primärschlüsselspalte. Beide sollten ein Register (KEY,
> INDEX) haben


Register? Das Dingens heißt Index oder meinetwegen auch Key.

> Laut EXPLAIN könnte die Abfrage das Register
> PRIMARY auch nutzen (Spalte "possible_keys"), tut es aber
> nicht (Spalte "key"). Warum nicht?


Der PRIMARY Index wird nur für die äußere Tabelle als möglicher Index
aufgeführt. Aber da es keine WHERE Bedingung gibt, müssen sowieso /alle/
Rows der äußeren Tabelle gelesen werden. Ein Index bringt da gar nichts.

Für die innere Tabelle (Index: KUNDE) : viel zu wenig Rows. Selbst ohne
JOIN-Buffer würde ein Lookup für eine von 4 Rows keinen Index verwenden.

Außerdem wird ein Block Nested Loop Join gemacht, der bei derart geringen
Datenmengen nur /einmal/ den JOIN-Buffer mit Rows aus der äußeren Tabelle
füllt und dann /einmal/ die Rows der inneren Tabelle gegen die JOIN-Spalte
aus dem Buffer vergleicht.

Guckstu:
Ähnliche Themen