[MySQL] LEFT JOIN extrem langsam

[Lokutos]

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
CREATE TABLE IF NOT EXISTS `laendercode` (
  `IP_F` int(10) unsigned NOT NULL DEFAULT '0',
  `IP_T` int(10) unsigned NOT NULL DEFAULT '0',
  `kurz` char(2) NOT NULL DEFAULT '',
  `land` varchar(30) NOT NULL,
  PRIMARY KEY (`IP_F`),
  UNIQUE KEY `IP_T` (`IP_T`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `user` (
  `kundenid` int(7) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Kundenid ',
  `ip` int(10) unsigned NOT NULL,
  PRIMARY KEY (`kundenid`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=205 ;

Daten:

user	184 Datensätze
laendercode	110000 Datensätze

Abfrage:

1:
Select user.kundenid, user.ip, laendercode.kurz, laendercode.land From user Left Join laendercode On user.ip BETWEEN laendercode.IP_F And  laendercode.IP_T

Das Problem eine abfrage dauert hier schon ewig

Zeige Datensätze 0 - 183 (184 insgesamt, die Abfrage dauerte 56.7203 sek.)

Ich weiss nicht wieso aber scheinbar nutzt er den index auf ip in der user Tabelle nicht.

ist hier ev. mal ein mysql Programmierer der mich da unterstützen könnte?
bitte nicht INNER JOIN vorschlagen da ich nich alle ip's in laendercode gespeichert habe.

Die IP wird als long wert gespeichert.

Besten dank
LKTechniks

[theHacker]

Du vergleichst ja mit > und <. Da kann ich mir schon vorstellen, dass da kein Index greift.

Schreib doch mal EXPLAIN vor die Abfrage, dann siehst du genau, was MySQL macht. Aber über 20 Millionen Datensätze kann schon ne Weile dauern, wenn du keinen Index parat hast.

[transversalis]

Einen Index auf die USER Tabelle kann er schon deshalb nicht verwenden, weil diese Tabelle der "composite table" ist, also die Tabelle, die er beim Join zuerst anlangt.
Das Vorgehen ist ja sinngemäß:
Hol Dir den ersten Satz der USER-Tabelle und suche alle dazu passenden Sätze aus der LAENDERCODE, dann hol Dir den zweiten Satz aus der USER und suche alle dazu passenden Sätze aus der LAENDERCODE, dann den dritten ...

Ein Index auf die USER-Tabelle wäre ja nur dann sinnvoll, wenn die Abfrage umgekehrt wäre ( hole die erste Zeile aus der LAENDERCODE und suche alle passenden Sätze aus der USER ...).
Theoretisch könnte ein Index auf die USER die Abfrage auch dann beschleunigen, wenn ein "Index-only" Zugriff möglich wäre.
( Also wenn der Index über IP und KundenID gehen würde, oder wenn in der Abfrage nicht die USER.KundenID abgefragt würde ).
Kannst ja zu Testzwecken mal die USER.KundenID aus der Abfrage weglassen und schauen, obs dann schneller ist.
( was ich allerdings bezweifle, wenn un der USER Tabelle wirklich nur 184 Sätze drin sind )

Als erstes würde ich allerdings mal einen Index über IP_F und IP_T legen ( ein Index über beide Spalten, nicht zwei getrennte Indices ) und schauen, ob mir das was bringt. Dann kannst Du auch einen Index über IP_F,IP_T,kurz,land legen. Das sollte am meisten bringen.

[ice-breaker]

Zitat von transversalis

Als erstes würde ich allerdings mal einen Index über IP_F und IP_T legen ( ein Index über beide Spalten, nicht zwei getrennte Indices ) und schauen, ob mir das was bringt.

bin ich auch dafür, wirkt am sinnvollsten

Zitat von transversalis

Dann kannst Du auch einen Index über IP_F,IP_T,kurz,land legen. Das sollte am meisten bringen.

da bin ich mir ehrlich gesagt nicht sicher, denn das würde den Index ziemlich aufblähen und somit mehr IO-Operationen benötigen, das gilt vor allem für das Land.
Man kann es aber natürlich mal ausprobieren.

[Lokutos]

Als erstes würde ich allerdings mal einen Index über IP_F und IP_T legen ( ein Index über beide Spalten, nicht zwei getrennte Indices ) und schauen, ob mir das was bringt.

Nicht wirklich messbare Fortschritte
mit den Indexen habe ich schon viel rumgespielt

das schnellste bisher ist n sub-select mit 21 sec
aber alles nicht das wahre.

[ice-breaker]

dann poste doch mal das Ergebnis des Querys mit EXPLAIN.

[Lokutos]

Zitat von ice-breaker

dann poste doch mal das Ergebnis des Querys mit EXPLAIN.

1:
2:
3:
4:
5:
EXPLAIN SELECT user.kundenid, user.ip, laendercode.kurz, laendercode.land
FROM user
LEFT JOIN laendercode ON user.ip
BETWEEN laendercode.IP_F
AND laendercode.IP_T

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	ALL	NULL	NULL	NULL	NULL	184
1	SIMPLE	laendercode	ALL PRIMARY,IP_T	NULL	NULL	NULL	110054

[ice-breaker]

MySQL-Version?
Schonmal mit nem Index-Hinweis (use) oder Zwang (force) probiert? [1] Bei alten MySQL-Versionen ist es manchmal ganz sinnvoll, wenn diese auf Grund "veralteter Algorithmen" Indexe nicht nutzen.

[1] http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

[theHacker]

Frage von mir: Kann man mit USE/FORCE INDEX überhaupt einen Index nutzen, der nicht bei possible_keys steht?

[ice-breaker]

keine Ahnung

den Fall, dass der Index nicht unter possible_keys aufgelistet war hatte ich noch nie

[transversalis]

Also, ich sehe gerade, dass -zumindest für DB2 z/OS- die Abfrage

WHERE spalte1 BETWEEN spalte2 AND spalte3

gar nicht stage1/indexfähig ist.

--> http://www.mayeruli.de/db2/stage1.html

Möglicherweise kann das dann mySQL auch nicht

[ice-breaker]

Optimizer does not use index for BETWEEN in a JOIN condition

gute Idee Transversalis
an einen Bug denke ich meist zuletzt.

[Lokutos]

hi
aktuell wenig Zeit.
links schaue ich mir später an.

Mysql Version ist
5.5.11