Re: Simpler Join: Komplette Tabelle wird gescannt
Hallo Gregor,
du erzwingst ein ORDER BY nach wetten.zeit_wettende. Wie soll MySQL dies machen? Es existiert kein Index auf diese Spalte also erfolgt eine Tablescan plus filesort um das Ergebnis korrekt liefern zu können. Weiterhin besteht der ORDER BY aus den Spalten von zwei Tabellen, da kann kein gemeinsamer Index existieren, es muß die gesamte Treffermenge sortiert werden.
mysql> EXPLAIN SELECT wetten.id
-> FROM wetten_wetten AS wetten
-> INNER JOIN wetten_wetten_teams AS wt1
-> ON wt1.wette = wetten.id
-> ORDER BY wetten.zeit_wettende, wt1.wette
-> LIMIT 5 ;
+----+-------------+--------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
| 1 | SIMPLE | wt1 | index | wette | wette | 4 | NULL | 64 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | wetten | eq_ref | PRIMARY | PRIMARY | 4 | sample.wt1.wette | 1 | |
+----+-------------+--------+--------+---------------+---------+---------+------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql>
Grüße
Thomas
Subject
Views
Written By
Posted
1895
December 15, 2012 02:12PM
1052
December 15, 2012 02:55PM
1017
December 15, 2012 03:09PM
1018
December 15, 2012 07:30PM
1120
December 16, 2012 02:32AM
Re: Simpler Join: Komplette Tabelle wird gescannt
944
December 16, 2012 02:41AM
851
December 22, 2012 09:02AM
1079
December 22, 2012 09:29AM
856
December 22, 2012 09:46AM
940
December 22, 2012 10:28AM
924
December 22, 2012 10:53AM
1207
December 22, 2012 11:05AM
1376
December 22, 2012 11:17AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.