join vs subselect

matthias — Tue, 19 Nov 2013 19:12:58 +0000

Vor einigen Jahren habe ich für den Mssql-Server herausgefunden, dass Abfragen mit “join” mehr als 100 mal schneller sein können als die gleich Abfrage in Form eines Subselects. Stimmt das auch heute für MySQL? Folgendes zeigt, dass es nicht zwingend so ist.

Frage: Welche Postleitzahlen gibt es sowohl in Deutschland als auch in den USA?

Stationen auf dem Weg zur Antwort:

Quelle von PLZ-dumps
http://www.tutorials.de/relationale-datenbanksysteme/229059-postleitzahlen-mysql-dump.html
=> Einspielen in die Tabelle ‘orte’.

Quelle US Zip-Codes
http://federalgovernmentzipcodes.us/free-zipcode-database.csv
aufbereiten mit
cut -d “,” -f2,4 free-zipcode-database.csv > free-zipcode-database-zip-city-only.csv
Ergänzung für einen brauchbaren MySQL-Dump überlasse ich dem geneigten Publikum.
=> Einspielen in die Tabelle ‘orteus’.

SQL-Befehle übersichtlich strukturiert. Sie werden unten als 1-Zeiler benutzt:

-- join
--
SELECT count(*)
FROM (
    SELECT distinct orte.PLZ FROM 
    orte, orteus
    where orte.PLZ = orteus.PLZ
) t1;

-- subselect
--
SELECT count(*)
FROM (
    SELECT distinct orte.PLZ
    FROM orte
    where orte.PLZ in (
        select orteus.PLZ FROM orteus
    )
) t2;

Copy&Paste aus dem mysql-Client:

mysql> select count(*) from orte;
 +----------+
 | count(*) |
 +----------+
 |    44188 |
 +----------+
 1 row in set (0.00 sec)

mysql> select count(*) from orteus;
+----------+
| count(*) |
+----------+
|    81831 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) FROM (SELECT distinct orte.PLZ FROM orte, orteus where orte.PLZ = orteus.PLZ) t1;
+----------+
| count(*) |
+----------+
|    13365 |
+----------+
1 row in set (0.16 sec)

mysql> SELECT count(*) FROM (SELECT distinct orte.PLZ FROM orte where orte.PLZ in (select orteus.PLZ FROM orteus)) t2;
+----------+
| count(*) |
+----------+
|    13365 |
+----------+
1 row in set (0.18 sec)

Ergebnis: Für ca. 44000×81000 ist fast kein Unterschied auszumachen.
Ich bin angenehm überrascht. Einschränkung der Gültigkeit der Erkenntnis: Die Datenmenge im obigen Beispiel ist nicht gross. Damals bei Mssql ging es um ungefähr 12000×625000, also Faktor 0,5 aber immerhin diesselbe Grössenordnung.

Groschengedanken zu Software » forschung

join vs subselect