Langsamer left outer join

[Ghostrider28]

Moin zusammen,

ich stehe ein klein wenig auf dem Schlauch.
Bisher habe ich noch nie mit Joins gearbeitet.

Nun habe ich zwei Tabellen, eine Benutzertabelle, wenige User ( ~ 10 ) und eine Tabelle mit Mobilfunkzellen IDs (~ 1.500.000).

Nun möchte ich anhand eines LEFT OUTER Joins (laut MySQL Doku dürfte der doch der Richtige sein) alle ZellenIDs aus Tabelle 2 ausgeben, die mit einer BenutzerID x verknüpft sind.
Leider dauert die Query sehr lange (18 Sekunden).

Die id's sind Primärschlüssel.

Gegeben sind folgende Tabellen:


Benutzertabelle:



| id | benutzer
---------------
| 1 | Name1
| 2 | Name2
| 3 | Name3
| 4 | Name4
| 5 | Name5
| 6 | Name6
| 7 | Name7


Zellentabelle:


| id | zellenID | benutzerID
-------------------------
| 1 | 111111111 | 3
| 2 | 222222222 | 1
| 3 | 333333333 | 5
| 4 | 444444444 | 7
| 5 | 555555555 | 6
| 6 | 666666666 | 4
| 7 | 777777777 | 2

(....)



Das ist bis dato meine Query:

SELECT A.id, A.benutzer, B.id, B.zellenID, B.benutzerID FROM benutzertabelle AS A LEFT OUTER JOIN zellentabelle AS B ON B.benutzerID = A.id WHERE A.benutzer = '1' ORDER by B.id DESC

Liegt der Geschwindigkeitsdefizit an meiner Query oder liegts eher an der Anzahl der verfügbaren ZellenIDs? 1.5 Mio. sollte doch eigentlich kein Problem sein, oder?

Danke im Voraus fürs Lesen und vielleicht auch für einen Tip ;-)

[code-kobold]

Liegt ein Index über B.benutzerID?

Lass dir mal

EXPLAIN SELECT...

ausgeben.

GR,

Ron

[Ghostrider28]

Habe das Ganze gerade mal an einer kleineren Tabelle probiert, Aufbau ist aber selbiger.

Tabelle 1 Benutzerdaten (ca. 10), andere Tabelle ca 600.000 Positionsdaten.

EXPLAIN SELECT A.tracking_id, A.unit, B.field_1, B.field_3, B.field_4, B.field_5, B.field_6, B.field_7, B.id
FROM tracking_ids AS A
INNER JOIN kd10001_on10001 AS B ON B.field_1 = A.tracking_id
WHERE A.reseller_id = '7'
ORDER BY B.id DESC
LIMIT 1

Resultat des Explains:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE A ALL tracking_id NULL NULL NULL 11 Using where; Using temporary; Using filesort
1 SIMPLE B ref field_1,indexField1 field_1 18 kd10001_on10001_.A.tracking_id 1316 Using where

Auf der Spalte mit den (wenigen) Geräte IDs liegt kein Index (Benutzertabelle), auf der Spalte mit den (vielen) Gerätes IDs (Positionstabelle) liegt einer.

[TBT]

auf A.reseller_id gehört ein Index!

besser noch (reseller_id,id) wegen der Sortierung

[AmicaNoctis]

Genauer gesagt, gehört ein Index auf alle Spalten, die in der ON-Klausel eines Joins oder üblicherweise in der Where-Klausel auftauchen. Darüber hinaus frage ich mich, ob du wirklich einen Left Join wolltest oder dir nicht vielleicht ein (inner) Join gereicht hätte, denn du wolltest ja

alle ZellenIDs aus Tabelle 2 ausgeben, die mit einer BenutzerID x verknüpft sind

Ein Left Join geht noch einen Schritt weiter, weil er ersmal alle Benutzer ausgibt und die ZellenIDs aus Tabelle 2 nur sofern vorhanden. Wenn dir ein (inner) Join reicht, kannst du die Tabellen auch vertauschen und dann kann MySQL sogar einen Equi-Join draus machen, der nochmal etwas schneller ist.

Gruß,

Amica

[Ghostrider28]

So, alle Spalten, die in einer ON Klausel oder einer WHERE Klausel vorkommen sind indiziert.

Habe es dann zunächst mit einem INNER JOIN sowie mit einem LEFT OUTER JOIN versucht, dauerte jeweils rund 17 Sekunden.

Habe es dann mal mit folgender Query versucht:

mysql> SELECT * FROM tracking_ids AS A, kd10001_on10001 AS B
-> WHERE A.reseller_id = '7' AND A.tracking_id = B.field_1 ORDER BY B.id DESC LIMIT 1

Und dies ist das Ergebnis:
1 row in set (1 min 51.67 sec)

Ich glaube, da habe ich gründlich was falsch gemacht.

[linuxfred]

Hallo,

ich bin auch ein Neuling, aber 4 Augen sehen manchmal mehr.

Frage:
Wie lange dauert die einfache Abfrage der ID der Table mit
den 1.500.000 Einträgen? (select ID from table)
(Ich gehe mal davon aus, dass Dein Datenbankserver ausreichend
Ressourcen hat.)

Ich habe derzeit kein shell-/php- script rumzuliegen, welches mir mal schnell
2.000.000 Einträge generiert sonst könnte ich sagen wie lange es bei mir
dauert.

Linuxfred

[Ghostrider28]

Ein reiner Select auf eine willkürliche (aber natürlich existierende) Geräte-ID (Feld: field_1):

SELECT * FROM kd10001_on10001 where field_1 = xyz

84203 rows in set (1.89 sec)

[onemorenerd]

Lade dir mal das Script von https://launchpad.net/mysql-tuning-primer runter und führe es aus. Es untersucht dein Setup und gibt Hinweise zur Optimierung.

Mir ist aufgefallen, dass du Strings als IDs verwendest. Das solltest du ändern, numerische Vergleiche sind schneller.