SQL Multiple column index. Wie nutzen?

[omaliesschen]

Hi,

Wie lässt sich der unique Key bei einer Abfrage nutzen?

CREATE TABLE IF NOT EXISTS `user_chat_parent_nodes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cid` varchar(34) COLLATE utf8_bin NOT NULL,
  `o_id` int(21) unsigned NOT NULL,
  `p_id` int(21) unsigned NOT NULL,
  `l` int(21) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `core` (`o_id`,`p_id`),
  KEY `cid_2` (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=128 ;

Zweite Frage. Warum lassen sich zwei Reihen mit identischer Chat_id einfügen?

INSERT INTO `u_nodes` (`id`, `cid`, `o_id`, `p_id`,  `l`) VALUES
(94, 'd09c2a8b40e790a2094ac1c7b8847dd1', 1, 7, 1369188147),
(95, 'd09c2a8b40e790a2094ac1c7b8847dd1', 7, 1, 1369188147)

Muss ein key nicht immer unique sein?

 

[NullPointer]

Zu 2: Nein, ein Key muss nur dann unique sein, wenn er PRIMARY oder UNIQUE ist.

Zu 1: Wenn du eine Abfrage wie

SELECT * FROM user_chat_parent_nodes WHERE owner_id = 42 AND partner_id = 1337

an die Datenbank schickt, dann wird der Index benutzt, um die passenden Zeilen herauszusuchen. Das geht gerade bei großen Tabellen deutlich schneller. IIRC ist dabei wichtig, dass die Spalten in der WHERE-Klausel in der gleichen Reihenfolge auftreten wie im Index.

 

[Daaron]

Hi,
Wie lässt sich der unique Key bei einer Abfrage nutzen?

Im Prinzip: Lass das mal schön die Sorge des RDBMS sein. Im Zweifel mit EXPLAIN(querie) gucken, ob er genutzt wird.
Suchvorgänge über einen Teil des Keys sollten gehen. Suchvorgänge über den gesamten Key (SELECT * FROM table WHERE owner_id=1 AND partner_id = 2) müssen den Key spätestens voll ausnutzen.

Zweite Frage. Warum lassen sich zwei Reihen mit identischer Chat_id einfügen?

Muss ein key nicht immer unique sein?

Nur PRIMARY KEY und UNIQUE KEY müssen einzigartig sein. Wäre ja sonst sinnlos. Denk mal über folgendes Konstrukt nach:
Tabelle von Userdaten, alle User haben eine Länder-Kennung (DE, AT,...):
SELECT * FROM users WHERE country="DE"...

Wenn country jetzt kein KEY ist, dann ist der Query lahm. Wenn country ein Unique-Key wäre, dann gäbe es nur einen Deutschen.

 

[omaliesschen]

Hatte etwas im Sinn wie SELECT x FROM y WHERE UniqueKey = (1,7).....

SELECT * FROM users WHERE country="DE"

Bisher dachte ich dass in dem Fall kein key möglich ist. Das wäre dann geklärt...

 

[Daaron]

Dann wären Keys, oder besser gesagt Indizes, ja fast nutzlos: bei allem außer dem Primary Key müsste ja bei jedem Select dann ein kompletter Table Scan durchgeführt werden. KEY ist einfach ein komisches Wort, besonders wenn man die Struktur mit einem Key-Value-Store vergleicht.

 

[Hancock]

Key: Für alles, was beim Sortieren mal gebraucht wird.
Unique Key: selbe wie Key PLUS du kannst damit verhindern, dass jemand Dupletten einfügt.

Keys: Sind eigentlich nur für die Geschwindigkeit gut, werden aber afaik bei MySQL gebraucht, falls du Constraints verwendest.
Unique keys: Schlüsselelement bei z.B. "insert into on duplicate key update" und Ähnlichem.

Du kannst auf CHAR(x),VARCHAR(x),TEXT,... kein vollen Schlüssel draufmachen, das bedeutet, dass du meistens nur die ersten x Bytes als Schlüssel verwendest.
Das kann gefährlich sein, da

create table t(
text varchar(255),
unique key `key`(`text`(5))
);
insert into t value
('testtesttest'),
('abcdef'),
('dfvnhsdfkljsn'),
('testtestblah')-- Hier krachts, da 'testt...' schon im Index ist, MySQL kann nicht zwischen dem ersten und dem Eintrag hier unterscheiden und meint, dass das eine Dublette ist

Daher als unique keys entweder Grenzen einbauen oder so entwickeln, dass es komplett nur mit INTs auskommt.

 

[ice-breaker]

ZIIRC ist dabei wichtig, dass die Spalten in der WHERE-Klausel in der gleichen Reihenfolge auftreten wie im Index.

Ich weiß, was du meinst, muss es aber korrigieren, weil deine Aussage falsch ist.
In welcher Reihenfolge die Bedingungen im WHERE auftreten ist egal, die kann ganz anders als die Index-Definition sein, so schlau sind Datenbanken schon!

Wenn man aber einen composite index hat, also ein Index der zugleich auf mehrere Spalten geht, wie z.B. ein Index der auf a, b und c geht, so kann der Index nur genutzt werden, wenn:

• man nach a sucht
• man nach a und b suche
• man nach a, b und c sucht

Der Index kann nicht genutzt werden, wenn man nur nach a und c sucht. Denn da fehlt der mittlere Teil des Index mit b. Einzig ein Keyscan kann den Index noch nutzen, aber das geht dann doch etwas zu weit.