Index nach OPTIMIZE wirkungslos?

Hallo,

ich verwzeifle hier gerade am Stezen von Indexen, auf der mySQl läuft
ein oscommerce shop, den verscueh ich gerade etwas zu beschleunigen.
Ich stoße jedoch auf ein Problem, was ich leider nicht erklären kann, so
gut kenne ich mich mit mySQL nun leider doch nicht aus.
Setze ich also einen Index in der Tabelle p2c, dann ergibt die Abfrage
folgendes Ergebnis:

mysql> EXPLAIN select distinct m.manufacturers_id as id,
m.manufacturers_name as name from products p, products_to_categories
p2c, products_to_stores p2s, manufacturers m, manufacturers_to_stores
m2s where p.products_status = '1' and p.manufacturers_id =
m.manufacturers_id and m.manufacturers_id = m2s.manufacturers_id and
p.products_id = p2c.products_id and p2c.categories_id = '380' and
p2s.stores_id = m2s.stores_id and m2s.stores_id = '1' order by
m.manufacturers_name;
+----+-------------+-------+--------+---------------------+- ----------
+---------+-----------------------------------+------
+----------------------------------------------------------- +
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra
|
+----+-------------+-------+--------+---------------------+- ----------
+---------+-----------------------------------+------
+----------------------------------------------------------- +
| 1 | SIMPLE | p2c | ref | PRIMARY,products_id | PRIMARY |
4 | const | 74 | Using where; Using index;
Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY |
4 | mwx_shop.p2c.products_id | 1 | Using where
|
| 1 | SIMPLE | m | ref | PRIMARY | PRIMARY |
4 | mwx_shop.p.manufacturers_id | 1 | Using index
|
| 1 | SIMPLE | m2s | eq_ref | PRIMARY | PRIMARY |
8 | mwx_shop.m.manufacturers_id,const | 1 | Using index; Distinct
|
| 1 | SIMPLE | p2s | ref | stores_id | stores_id |
4 | mwx_shop.m2s.stores_id | 148 | Using index; Distinct
|
+----+-------------+-------+--------+---------------------+- ----------
+---------+-----------------------------------+------
+----------------------------------------------------------- +
5 rows in set (0.00 sec)


So weit so gut, lasse ich jetzt
ANALYZE TABLE `products_to_stores`;
REPAIR TABLE `products_to_stores`;
SHOW TABLE STATUS FROM `mwx_shop`;
OPTIMIZE TABLE `products_to_stores`;
SHOW TABLE STATUS FROM `mwx_shop`;
FLUSH TABLE `mwx_shop`.`products_to_stores`;

laufen, dann ergibt die gleiche Abfrage:

mysql> EXPLAIN select distinct m.manufacturers_id as id,
m.manufacturers_name as name from products p, products_to_categories
p2c, products_to_stores p2s, manufacturers m, manufacturers_to_stores
m2s where p.products_status = '1' and p.manufacturers_id =
m.manufacturers_id and m.manufacturers_id = m2s.manufacturers_id and
p.products_id = p2c.products_id and p2c.categories_id = '380' and
p2s.stores_id = m2s.stores_id and m2s.stores_id = '1' order by
m.manufacturers_name;
+----+-------------+-------+--------+---------------------+- ----------
+---------+-----------------------------------+-------
+----------------------------------------------------------- +
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra
|
+----+-------------+-------+--------+---------------------+- ----------
+---------+-----------------------------------+-------
+----------------------------------------------------------- +
| 1 | SIMPLE | p2c | ref | PRIMARY,products_id | PRIMARY |
4 | const | 74 | Using where; Using
index; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY |
4 | mwx_shop.p2c.products_id | 1 | Using where
|
| 1 | SIMPLE | m | ref | PRIMARY | PRIMARY |
4 | mwx_shop.p.manufacturers_id | 1 | Using index
|
| 1 | SIMPLE | m2s | eq_ref | PRIMARY | PRIMARY |
8 | mwx_shop.m.manufacturers_id,const | 1 | Using index; Distinct
|
| 1 | SIMPLE | p2s | index | stores_id | stores_id |
8 | NULL | 11144 | Using where; Using
index; Distinct |
+----+-------------+-------+--------+---------------------+- ----------
+---------+-----------------------------------+-------
+----------------------------------------------------------- +
5 rows in set (0.00 sec)

Warum verliert er da die ref?
Ich muss jetzt den Index rausnhemen udn wieder reinnehmen, dann habe ich
wieder erstes Ergebnis.

Ich hoffe mir kann da jemand einen Ansatz geben, da ich das nicht ganz
verstehe.

Ciao
André

Re: Index nach OPTIMIZE wirkungslos?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Index nach OPTIMIZE wirkungslos?

Andreas Kretschmer schrieb:
> Frage: kommt das wirklich so halbverdaut raus, oder ist das nur Dein
> Newsclient, der sich hier erbricht?

bei entsprechend schmalem Clientfenster ... ;-)

W

Re: Index nach OPTIMIZE wirkungslos?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Index nach OPTIMIZE wirkungslos?

Andreas Kretschmer schrieb:

> begin "Andr=E9 Schneider" wrote:
> > Setze ich also einen Index in der Tabelle p2c, dann ergibt die Abfrage
> > folgendes Ergebnis:
>
> Indexe setzt man nicht auf Tabellen, sondern auf Spalten.

Wo steht denn, dass ich den Index auf eine Tabelle gesetzt habe? Da
steht IN der Tabelle.

> > mysql> EXPLAIN select distinct m.manufacturers_id as id,
> > ...
> > m.manufacturers_name;
> > +----+-------------+-------+--------+---------------------+- ----------
> > +---------+-----------------------------------+------
> > +----------------------------------------------------------- +
>
> Davon abgesehen, daß das Explain von MySQL einklich nur als Brechmittel
> taugt, hast Du es durch Deinen kapotten Umbruch im Brechreiz noch
> gesteigert. Respekt.
> Frage: kommt das wirklich so halbverdaut raus, oder ist das nur Dein
> Newsclient, der sich hier erbricht?

Kopiere dir doch bitte die Ausgabe mal in ein Textprogramm deiner Wahl,
dann sieht es besser aus.
Lässt sich mit den 72 Zeichen ds Newsclientes so nicht darsetllen, das
stimmt leider.

> > So weit so gut, lasse ich jetzt
> > ANALYZE TABLE `products_to_stores`;
> > REPAIR TABLE `products_to_stores`;
>
> Warum einklich Repair? War/ist die Table kapott?

Was steht im Betreff?

> > laufen, dann ergibt die gleiche Abfrage:
>
> > mysql> EXPLAIN select distinct m.manufacturers_id as id,
> > ...
> > m.manufacturers_name;
> > +----+-------------+-------+--------+---------------------+- ----------
> > +---------+-----------------------------------+-------
> > +----------------------------------------------------------- +
>
> *würg*

Was schlechtes gegessen?

> > Ich hoffe mir kann da jemand einen Ansatz geben, da ich das nicht ganz
> > verstehe.
>
> Sorry, muß ich passen. Offensichtlich versuchst Du erfolglos MySQL zu
> mißbrauchen.
>
> Für komplexere Anfrage, als die es gemacht wurde.

Hast du mein Posting überhaupt gelesen?

Egal, trotzdem danke.

Ciao
Andr=E9

Re: Index nach OPTIMIZE wirkungslos?

André Schneider schrieb:

> Andreas Kretschmer schrieb:

(Kaputtes Kretschmer-Quoting manuell korrigiert)

>> "André Schneider" wrote:
>>
>>> Setze ich also einen Index in der Tabelle p2c, dann ergibt die Abfrage
>>> folgendes Ergebnis:
>>
>> Indexe setzt man nicht auf Tabellen, sondern auf Spalten.
>
> Wo steht denn, dass ich den Index auf eine Tabelle gesetzt habe? Da
> steht IN der Tabelle.

Aber nicht, auf welche Spalte. Dem EXPLAIN nach zu urteilen, hast Du
sogar mehrere Indizes gesetzt.

> Was steht im Betreff?

"Index nach OPTIMIZE wirkungslos?". Warum fragst Du?

>> Sorry, muß ich passen. Offensichtlich versuchst Du erfolglos MySQL zu
>> mißbrauchen.
>>
>> Für komplexere Anfrage, als die es gemacht wurde.

Typisch Andreas.

Gruß. Claus
--
,~°O O
O ,´ / |/|\
/ |¯`. Das neue Hochzeits-Branchenbuch im Internet ,´ / | |\
/__| `~...............................................~´ /___|/ /

Re: Index nach OPTIMIZE wirkungslos?

André Schneider wrote:
^ Besser als im Originalposting. (Dort war es nämlich falsch kodiert
und daher unlesbar.)


>> Davon abgesehen, daß das Explain von MySQL einklich nur als
>> Brechmittel taugt, hast Du es durch Deinen kapotten Umbruch im
>> Brechreiz noch gesteigert. Respekt.
>> Frage: kommt das wirklich so halbverdaut raus, oder ist das nur Dein
>> Newsclient, der sich hier erbricht?
>
> Kopiere dir doch bitte die Ausgabe mal in ein Textprogramm deiner Wahl,
> dann sieht es besser aus.
> Lässt sich mit den 72 Zeichen ds Newsclientes so nicht darsetllen, das
> stimmt leider.

Schließt Du die Eingabe mit \G statt mit ; ab, kommt es in einem
"usenet-kompatiblen" Format. Das wollte Andreas Dir auch mitteilen, hat
aber irrtümlich den Gerber-Mode aktiviert...


>> > So weit so gut, lasse ich jetzt
>> > ANALYZE TABLE `products_to_stores`;
>> > REPAIR TABLE `products_to_stores`;
>>
>> Warum einklich Repair? War/ist die Table kapott?
>
> Was steht im Betreff?

Nichts, was mit REPAIR zusammenhängt. Daher vermutlich die Frage...


>> >
+----+-------------+-------+--------+---------------------+- ----------
>> > +---------+-----------------------------------+-------
>> > +----------------------------------------------------------- +
>>
>> *würg*
>
> Was schlechtes gegessen?

Von zu vielen '-' bekommt man Magenverstimmungen.


Zurück zum Thema:

Ich kann es Dir auch nicht genau sagen, aber tritt das Problem auch dann
noch auf, nachdem Du am Schluß ein ANALYZE TABLE durchführst?

Denn der Sinn Deiner Kaskade

| ANALYZE TABLE `products_to_stores`;
| REPAIR TABLE `products_to_stores`;
| SHOW TABLE STATUS FROM `mwx_shop`;
| OPTIMIZE TABLE `products_to_stores`;
| SHOW TABLE STATUS FROM `mwx_shop`;
| FLUSH TABLE `mwx_shop`.`products_to_stores`;

erschließt sich mir nicht ganz. Wenn, dann gehört das ANALYZE TABLE an
den Schluß, denn da werden die Indices neu gewichtet (sorr, Fachbegriff
grade nicht parat), so daß der Optimizer Kenntnis über den besten Index
zu einer gegebenen Anfrage bekommt. Hat er die nicht, beurteilt er die
Situation u.U. falsch und wählt nicht die optimale Strategie.


Thomas
--
Jabber-ID: glglgl@amessage.info (keine Email-Adresse!)
Warum Jabber, was ist das und wie geht das?
http://de.wikibooks.org/wiki/Jabber-Kompendium:_Schnelleinst ieg

Re: Index nach OPTIMIZE wirkungslos?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Index nach OPTIMIZE wirkungslos?

Thomas Rachel wrote in news:eos9il
$mvd$1@glglgl.ath.cx:

>
> Zurück zum Thema:
>
> Ich kann es Dir auch nicht genau sagen, aber tritt das Problem auch dann
> noch auf, nachdem Du am Schluß ein ANALYZE TABLE durchführst?

Es tritt auch auf, wenn ich nur ANALYZE durchführe oder nur OPTIMIZE oder
nur REPAIR.
Das einzige was geht ist FLUSH.

> Denn der Sinn Deiner Kaskade
>
>| ANALYZE TABLE `products_to_stores`;
>| REPAIR TABLE `products_to_stores`;
>| SHOW TABLE STATUS FROM `mwx_shop`;
>| OPTIMIZE TABLE `products_to_stores`;
>| SHOW TABLE STATUS FROM `mwx_shop`;
>| FLUSH TABLE `mwx_shop`.`products_to_stores`;
>
> erschließt sich mir nicht ganz. Wenn, dann gehört das ANALYZE TABLE an
> den Schluß, denn da werden die Indices neu gewichtet (sorr, Fachbegriff
> grade nicht parat), so daß der Optimizer Kenntnis über den besten Index
> zu einer gegebenen Anfrage bekommt. Hat er die nicht, beurteilt er die
> Situation u.U. falsch und wählt nicht die optimale Strategie.

Hier nochmals die Ausgabe etwas leserlicher:

So soll es sein:

1|SIMPLE|p2s|ref|PRIMARY| PRIMARY|8|NULL|148|Using index; Distinct

und das wird nach dem Ausführen von ANALYZE etc. daraus:

1|SIMPLE|p2s|index|PRIMARY| PRIMARY|8|NULL|11145|Using where; Using index;
Distinct

Im Notfall setze ich halt die Indizes nach dem Optimieren immer wieder neu,
kann man dafür einen Cron Job einrichten ;-)

Ciao
André

Re: Index nach OPTIMIZE wirkungslos?

Andreas Kretschmer wrote in
news:jai984-4l7.ln1@news.a-kretschmer.de:

> Aso. Und dazu 3 Katzen opfern, ja?
> Hase, es gibt auch Newsprogramme, die einfach so funktionieren. Klingt
> lustig, ist aber so. Wenn ich etwas einfügen will, was breiter als 72
> ist, sage ich einfach "ESC:set tw=0" und "ESC:set tw=72" um wieder auf
> 72 zu schalten. Das mache ich, damit meine Leserschaft es einfach hat.
> (Falls Leser kapotte Software hat, die das dann verschlimmbessert,
> dann ist das nicht mehr meine Verantwortung)
>
> Deine Idee, Hilfe zu erwarten und gleichzeitig von _ALLEN_
> potentiellen Helfern zu verlangen, diese mögen erst mal 3 Katzen
> opfern, ist schlicht frech.

Du wirst leicht OT. Ich empfehle eine Tiergroup wegen der Katzen oder
irgend was okultes, wegen dem Opfern der selbigen. Möge jemand ein follow
up to.. setzen.
Frech sind nicht meine Fragen, aber ein bisschen deine Antworten, aber
egal, trotzdem Danke, hättest ja mein Posting nicht lesen müssen. ;-)

Ciao
André

Re: Index nach OPTIMIZE wirkungslos?

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Index nach OPTIMIZE wirkungslos?

Andreas Kretschmer wrote in
news:612a84-74n.ln1@news.a-kretschmer.de:


> Der Index ist doch noch da, er wird nur nicht genutzt. Und wenn Du
> wirklich der Meinung bist, schlauer als der Planner zu sein, kann man
> immer noch mit 'set enable_seqscan = false;'¹ versuchen, ihn zu
> zwingen. Oder mit der Konfiguration der Abschätzung von Kosten für
> untersch. Operationen. Aber ob und wie das mit MySQL geht, solltest Du
> der Doku entnehmen können.

Naja, die Parsetime steigt nach dem Ausführen von OPTIMIZE etc. auf
ungefähr 1,8 Sekunden, zuvor waren es knapp 0,5.
Ich werde mir das nochmals genau anschauen, es müsste ja Dokus dazu geben.

Danke & Ciao
André

Re: Index nach OPTIMIZE wirkungslos?

"André Schneider" wrote:

Vorerst muß ich mich der Kritik meiner Vorredner anschließen, wegen
deiner mangelhaften Darstellung dessen, was du für ein Problem hältst.

> mysql> EXPLAIN select distinct m.manufacturers_id as id,
> m.manufacturers_name as name from products p, products_to_categories
> p2c, products_to_stores p2s, manufacturers m, manufacturers_to_stores
> m2s where p.products_status = '1' and p.manufacturers_id =
> m.manufacturers_id and m.manufacturers_id = m2s.manufacturers_id and
> p.products_id = p2c.products_id and p2c.categories_id = '380' and
> p2s.stores_id = m2s.stores_id and m2s.stores_id = '1' order by
> m.manufacturers_name;

Das fängt schon mal damit an, daß du (für Menschen) unlesbares SQL
schreibst. Ich nehme mal zu deiner Verteidigung an, daß du das nur
zitierst. Wenn nicht: statt Komma-JOIN mit JOIN-Bedingung(en) in WHERE
ist JOIN ... ON deutlich besser lesbar. Außerdem kann man SQL auch
einrücken und Schlüsselworte GROSS schreiben.


Nun zu deinem Problem (bzw. was du für eins hältst)

>| id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra
....
>| 1 | SIMPLE | p2s | ref | stores_id | stores_id | > 4 | mwx_shop.m2s.stores_id | 148 | Using index; Distinct

vs.

>| id | select_type | table | type | possible_keys | key | > key_len | ref | rows | Extra
....
>| 1 | SIMPLE | p2s | index | stores_id | stores_id | 8 | NULL | 11144 | Using where; Using index; Distinct

> Warum verliert er da die ref?

Ich habe einige Zeit gebraucht, bis ich verstanden habe, was du hier
sagen willst. So ein kleiner Schubs in Richtung "für den Zugriff auf
Tabelle p2s" wäre wirklich hilfreich gewesen.

Immerhin opfern wir hier *unsere* Zeit, um dir bei *deinem* Problem
(bzw. dem was du für eins hältst) zu helfen. Wäre es da nicht fair und
angemessen, es uns leicht zu machen?

> Ich muss jetzt den Index rausnhemen udn wieder reinnehmen, dann habe ich
> wieder erstes Ergebnis.

Zuerst mal solltest du dir eine Rechschreibprüfung zulegen. Alternativ
gleich orthographisch korrekt schreiben.

> Ich hoffe mir kann da jemand einen Ansatz geben, da ich das nicht ganz
> verstehe.

Ich schlage vor, du liest erstmal:


Dann wird dir auffallen, daß der Optimizer nach der Aktualisierung der
Selektivität der vorhandenen Indizes - das passiert bei ANALYZE TABLE
und als Abfallprodukt auch bei REPAIR TABLE - einen anderen Plan wählt
als vorher. Statt für jede Zeile im JOIN-Zwischenergebnis die Zeilen
aus `p2s` mit matchender `stores_id` zu holen (Zugriffsmethode 'ref')
wählt der Optimizer einen Indexscan ('index'). Vermutlich sind die
geschätzten 11144 Treffer pro Zugriff schon ein zu großer Anteil an der
`p2s` Tabelle, so daß sich der wahlfreie Zugriff im Vergleich zu einem
sequentiellen Scan nicht rechnet. Außerdem kann so auch gleich die
Bedingung stores_id=1 mit erledigt werden ('using where').

Leider sagst du nichts darüber, ob der neue Plan für dich tatsächlich
schlechter ist als der alte (sprich: ob die Query langsamer wird).
Wenn die Daten sehr ungleichmäßig verteilt sind, kann der Optimizer
schon mal daneben liegen. Vorher solltest du deine Query aber auch
mal mit verschiedenen Werten ausprobieren. Womöglich ist sie ja für
stores_id=1 in der alten, für stores_id=42 in der neuen Variante
schneller. Wenn der Optimizer konstant falsch liegt, solltest du

1. einen Bugreport schreiben bei
2. entweder den Index wieder entfernen, oder, wenn du den für eine
andere Query brauchst, die problematische Query um ein passendes
IGNORE INDEX ... anreichern. Details verrät dir das Handbuch:




XL

Re: Index nach OPTIMIZE wirkungslos?

Axel Schwenke wrote in
news:6o90pe.fl4.ln@xl.homelinux.org:

> "André Schneider" wrote:
>
> Vorerst muß ich mich der Kritik meiner Vorredner anschließen, wegen
> deiner mangelhaften Darstellung dessen, was du für ein Problem hältst.

Sorry für die holprige Rehctshcriebung, ich hatte noch versucht das
Posting mit einer korrigierten Version zu überschrieben, aber mein
Newsserver lässt das scheinbar nicht zu.

> Nun zu deinem Problem (bzw. was du für eins hältst)
>
>>| id | select_type | table | type | possible_keys | key | >
>>| key_len | ref | rows | Extra
> ...
>>| 1 | SIMPLE | p2s | ref | stores_id | stores_id |
>>| > 4 | mwx_shop.m2s.stores_id | 148 | Using index; Distinct
>
> vs.
>
>>| id | select_type | table | type | possible_keys | key | >
>>| key_len | ref | rows | Extra
> ...
>>| 1 | SIMPLE | p2s | index | stores_id | stores_id |
>>| 8 | NULL | 11144 | Using where; Using index; Distinct
>
>> Warum verliert er da die ref?
>
> Ich habe einige Zeit gebraucht, bis ich verstanden habe, was du hier
> sagen willst. So ein kleiner Schubs in Richtung "für den Zugriff auf
> Tabelle p2s" wäre wirklich hilfreich gewesen.

Upps, stimmt.

> Ich schlage vor, du liest erstmal:
>

Ok, wird eledigt.

> Leider sagst du nichts darüber, ob der neue Plan für dich tatsächlich
> schlechter ist als der alte (sprich: ob die Query langsamer wird).
> Wenn die Daten sehr ungleichmäßig verteilt sind, kann der Optimizer
> schon mal daneben liegen. Vorher solltest du deine Query aber auch
> mal mit verschiedenen Werten ausprobieren. Womöglich ist sie ja für
> stores_id=1 in der alten, für stores_id=42 in der neuen Variante
> schneller. Wenn der Optimizer konstant falsch liegt, solltest du

Also derzeit gibt es nur stores_id=1 und die Parsetime ist nach der
Optimierung deutlich höher als davor. Kann schon sein dass er davon
ausgeht, dass es ja auch mal mehr stores_ids geben wird.

Danke & Ciao
André

Re: Index nach OPTIMIZE wirkungslos?

"André Schneider" wrote in news:Xns98C04FE49D981aasnde@
216.168.3.50:

> Also derzeit gibt es nur stores_id=1 und die Parsetime ist nach der
> Optimierung deutlich höher als davor. Kann schon sein dass er davon
> ausgeht, dass es ja auch mal mehr stores_ids geben wird.

Gerade nochmals getestet, die Parsetime ist nach der Optimierung bei obiger
Abfrage sogar teilweise über 20 sek.
Ohne die Optimierung eben so um die 0,5 sek.
Ich denke dass das doch irgend ein Fehler sein muss, oder kann sich die db
so irren?

Ich werde mal auf einem Testsystem MySQL 5 probieren, vielleicht geht es
damit.

Ciao
André

Re: Index nach OPTIMIZE wirkungslos?

Axel Schwenke schrieb:
[...]
>
> [...] statt Komma-JOIN mit JOIN-Bedingung(en) in WHERE
> ist JOIN ... ON deutlich besser lesbar.
> [...]

Das ist jetzt erstmal Deine persönliche Meinung.
Ich finde dieses ganze "join on"-Gemurkse z.B. zum Erbrechen
....

Michael

Re: Index nach OPTIMIZE wirkungslos?

=?ISO-8859-1?Q?Michael_König?= wrote:
> Axel Schwenke schrieb:
>>
>> [...] statt Komma-JOIN mit JOIN-Bedingung(en) in WHERE
>> ist JOIN ... ON deutlich besser lesbar.
>
> Das ist jetzt erstmal Deine persönliche Meinung.

Aber eine fundierte:

JOIN ... ON erzwingt die saubere Trennung zwischen JOIN-Bedingungen
und Selektions-Bedingungen. Das verhindert z.B. zuverlässig, daß man
eine JOIN-Bedingung vergißt. Außerdem wird die Struktur des JOINs
klarer. Ist das eine Kette oder ein Stern oder was?

Sobald man OUTER JOINs verwendet, reicht der Komma-Operator sowieso
nicht mehr und - schlimmer - MySQL 5+ gibt JOIN eine höhere Priorität
als "," was zu unerwarteten Syntax-Fehlern führen kann, wenn man JOIN
und Komma-Operator in einer Query verwendet.


Nehmen wir mal die Query des OP als Beispiel:

select distinct m.manufacturers_id as id, m.manufacturers_name as name
from products p, products_to_categories p2c, products_to_stores p2s,
manufacturers m, manufacturers_to_stores m2s where p.products_status =
'1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id
= m2s.manufacturers_id and p.products_id = p2c.products_id and
p2c.categories_id = '380' and p2s.stores_id = m2s.stores_id and
m2s.stores_id = '1' order by m.manufacturers_name

vs.

SELECT DISTINCT
m.manufacturers_id AS id,
m.manufacturers_name AS name
FROM products AS p,
INNER JOIN products_to_categories AS p2c
ON p2c.products_id = p.products_id
INNER JOIN manufacturers AS m
ON m.manufacturers_id = p.manufacturers_id
INNER JOIN manufacturers_to_stores AS m2s
ON m2s.manufacturers_id = m.manufacturers_id
INNER JOIN products_to_stores AS p2s
ON p2s.stores_id = m2s.stores_id
WHERE p.products_status = '1'
AND p2c.categories_id = '380'
AND m2s.stores_id = '1'
ORDER BY m.manufacturers_name

Jetzt erst(!) fällt mir z.B. auf, daß der JOIN mit `products_to_stores`
weitgehend sinnlos ist. Es wird weder ein Feld dieser Tabelle abgefragt
noch nach einem Feld dieser Tabelle eingeschränkt. Es wird lediglich
geprüft, ob es da überhaupt einen Eintrag zur stores_id gibt.

Hier ein Baum, wie die Tabellen zusammenhängen und wofür jede Tabelle
gebraucht wird:

[products] WHERE products_status=1
^
'--> [products_to_categories] WHERE categories_id=380
^
'--> [manufacturers] SELECT id, name
^
'--> [manufacturers_to_stores] WHERE stores_id=1
^
'--> [products_to_stores]

Die Abfrage ist: "finde Id und Namen aller Hersteller, die mindestens
ein Produkt mit Status 1 aus Kategorie 380 herstellen und Laden 1
beliefern, aber nur wenn Laden 1 irgendein Produkt verkauft"


Vermutlich war ursprünglich eher das hier geplant:

[products] WHERE products_status=1
^
'-------> [products_to_categories] WHERE categories_id=380
^
'--> [manufacturers] SELECT id, name
^
'--> [manufacturers_to_stores] WHERE stores_id=1
^ ^
'--> [p2s] <--'

"finde Id und Namen aller Hersteller, die mindestens ein Produkt mit
Status 1 aus Kategorie 380 herstellen und dieses an Laden 1 liefern"


> Ich finde dieses ganze "join on"-Gemurkse z.B. zum Erbrechen

Kannst du das auch begründen?


XL

Re: Index nach OPTIMIZE wirkungslos?

Axel Schwenke schrieb:
> =?ISO-8859-1?Q?Michael_König?= wrote:
>
>>Axel Schwenke schrieb:
>>
>>>[...] statt Komma-JOIN mit JOIN-Bedingung(en) in WHERE
>>>ist JOIN ... ON deutlich besser lesbar.
>>
>>Das ist jetzt erstmal Deine persönliche Meinung.
>
>
> Aber eine fundierte:
>
> JOIN ... ON erzwingt die saubere Trennung zwischen JOIN-Bedingungen
> und Selektions-Bedingungen. Das verhindert z.B. zuverlässig, daß man
> eine JOIN-Bedingung vergißt.

Dagegen hilft dann das mit dem Konzentration bei der Arbeit ;-)

[...]

> select distinct m.manufacturers_id as id, m.manufacturers_name as name
> from products p, products_to_categories p2c, products_to_stores p2s,
> manufacturers m, manufacturers_to_stores m2s where p.products_status =
> '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id
> = m2s.manufacturers_id and p.products_id = p2c.products_id and
> p2c.categories_id = '380' and p2s.stores_id = m2s.stores_id and
> m2s.stores_id = '1' order by m.manufacturers_name

Fairerweise müsste man hier schreiben:
select distinct m.manufacturers_id as id,
m.manufacturers_name as name
from products p,
products_to_categories p2c,
products_to_stores p2s,
manufacturers m,
manufacturers_to_stores m2s
where p.products_status = '1'
and p2c.categories_id = '380'
and m2s.stores_id = '1'
and p.manufacturers_id = m.manufacturers_id
and m.manufacturers_id = m2s.manufacturers_id
and p.products_id = p2c.products_id
and p2s.stores_id = m2s.stores_id
order by m.manufacturers_name


> vs.
>
> SELECT DISTINCT
> m.manufacturers_id AS id,
> m.manufacturers_name AS name
> FROM products AS p,
> INNER JOIN products_to_categories AS p2c
> ON p2c.products_id = p.products_id
> INNER JOIN manufacturers AS m
> ON m.manufacturers_id = p.manufacturers_id
> INNER JOIN manufacturers_to_stores AS m2s
> ON m2s.manufacturers_id = m.manufacturers_id
> INNER JOIN products_to_stores AS p2s
> ON p2s.stores_id = m2s.stores_id
> WHERE p.products_status = '1'
> AND p2c.categories_id = '380'
> AND m2s.stores_id = '1'
> ORDER BY m.manufacturers_name

und ich finde die obige Version besser verständlich und besser lesbar.
(alle beteiligten Tabellen sind beieinander und alle Bedingungen sind
bei einander. Zugegeben, ich habe mir auch angewöhnt, zuerst die
Auswahl- und dann die Verknüpfungskriterien aufzulisten.
>
>>Ich finde dieses ganze "join on"-Gemurkse z.B. zum Erbrechen
>
>
> Kannst du das auch begründen?

Ja, ich habe die Komma-Schreibweise von Anfang an verwendet und bin
diese Schreibweise gewöhnt (und das sei hier auch erwähnt, ich verwende
sie täglich unfallfrei), während ich beim Lesen der Join-Schreibweise
immer wieder ins Stolpern komme und die Abfragen meist erst dann
verstehe, wenn ich sie vernünftig umschreibe.. ;-)

Gruß,
Michael

Re: Index nach OPTIMIZE wirkungslos?

Michael König wrote:

> Ja, ich habe die Komma-Schreibweise von Anfang an verwendet und bin
> diese Schreibweise gewöhnt (und das sei hier auch erwähnt, ich verwende
> sie täglich unfallfrei), während ich beim Lesen der Join-Schreibweise
> immer wieder ins Stolpern komme und die Abfragen meist erst dann
> verstehe, wenn ich sie vernünftig umschreibe.. ;-)

Und wie machst Du dann OUTER JOINs?


Thomas
--
Jabber-ID: glglgl@amessage.info (keine Email-Adresse!)
Warum Jabber, was ist das und wie geht das?
http://de.wikibooks.org/wiki/Jabber-Kompendium:_Schnelleinst ieg

Re: Index nach OPTIMIZE wirkungslos?

Thomas Rachel schrieb:
> Michael König wrote:
>
>
>>Ja, ich habe die Komma-Schreibweise von Anfang an verwendet und bin
>>diese Schreibweise gewöhnt (und das sei hier auch erwähnt, ich verwende
>>sie täglich unfallfrei), während ich beim Lesen der Join-Schreibweise
>>immer wieder ins Stolpern komme und die Abfragen meist erst dann
>>verstehe, wenn ich sie vernünftig umschreibe.. ;-)
>
>
> Und wie machst Du dann OUTER JOINs?
>
>
> Thomas

Na, wie man das halt macht:

select * from personen as p, adressen as a
where p.p_id = a.a_p_id (+)
and ...

Wo ist das Problem?

Michael

Re: Index nach OPTIMIZE wirkungslos?

Michael König wrote:

>> Und wie machst Du dann OUTER JOINs?
>>
>>
>> Thomas
>
> Na, wie man das halt macht:
>
> select * from personen as p, adressen as a
> where p.p_id = a.a_p_id (+)
> and ...
>
> Wo ist das Problem?

Hm.

CREATE TEMPORARY TABLE a (id INT, value INT);
INSERT INTO a VALUES (1,1000),(2,2000);

CREATE TEMPORARY TABLE b (id INT, value INT);
INSERT INTO b VALUES (1,5000);

SELECT * FROM a JOIN b USING (id)\G
SELECT * FROM a, b WHERE a.id=b.id\G
-- das sind INNER JOINs: beide ->
*************************** 1. row ***************************
id: 1
value: 1000
id: 1
value: 5000
1 row in set (0,00 sec)

Soweit so gut.

SELECT * FROM a LEFT JOIN b USING (id)\G -- man beachte das LEFT JOIN!
*************************** 1. row ***************************
id: 1
value: 1000
id: 1
value: 5000
*************************** 2. row ***************************
id: 2
value: 2000
id: NULL
value: NULL
2 rows in set (0,01 sec)

Das meinte ich.

Deine vorgeschlagene Syntax

SELECT * FROM a, b WHERE a.id=b.id (+)\G

funktioniert so nicht. Hätte mich auch gewundert. Was Du mit (+) sagen
wolltest - keine Ahnung.


Thomas
--
Jabber-ID: glglgl@amessage.info (keine Email-Adresse!)
Warum Jabber, was ist das und wie geht das?
http://de.wikibooks.org/wiki/Jabber-Kompendium:_Schnelleinst ieg

Re: Index nach OPTIMIZE wirkungslos?

=?UTF-8?B?TWljaGFlbCBLw7ZuaWc=?= wrote:
> Thomas Rachel schrieb:
>>
>> Und wie machst Du dann OUTER JOINs?
>
> select * from personen as p, adressen as a
> where p.p_id = a.a_p_id (+)
> and ...
>
> Wo ist das Problem?

Daß das kein OUTER JOIN ist?


XL

Re: Index nach OPTIMIZE wirkungslos?

Michael König schrieb:

>> Und wie machst Du dann OUTER JOINs?
> ...
> Na, wie man das halt macht:
>
> select * from personen as p, adressen as a
> where p.p_id = a.a_p_id (+)
> and ...

Entweder sind meine SQL-Kenntnisse so schlecht, dass mir die Bedeutung
des (+) in obiger Query nicht bekannt ist. Oder deine, dass du nicht
weißt, was ein OUTER JOIN ist.

gruss, heli

Re: Index nach OPTIMIZE wirkungslos?

Axel Schwenke schrieb:
> =?UTF-8?B?TWljaGFlbCBLw7ZuaWc=?= wrote:
>
>>Thomas Rachel schrieb:
>>
>>>Und wie machst Du dann OUTER JOINs?
>>
>>select * from personen as p, adressen as a
>>where p.p_id = a.a_p_id (+)
>>and ...
>>
>>Wo ist das Problem?
>
>
> Daß das kein OUTER JOIN ist?
>
>
> XL

Sondern was?

Gruß,
Michael

Re: Index nach OPTIMIZE wirkungslos?

Michael König wrote:
> Axel Schwenke schrieb:
>> =?UTF-8?B?TWljaGFlbCBLw7ZuaWc=?= wrote:
>>
>>> Thomas Rachel schrieb:
>>>
>>>> Und wie machst Du dann OUTER JOINs?
>>>
>>> select * from personen as p, adressen as a
>>> where p.p_id = a.a_p_id (+)
>>> and ...
>>>
>>> Wo ist das Problem?
>>
>>
>> Daß das kein OUTER JOIN ist?
>>
>>
>> XL
>
> Sondern was?

[ ] Du weißt, wo du bist.
[X] Du willst nur trollen.

Niemanden hier interessiert irgendeine sonderbare Oracle-Syntax. Wenn du
deine Argumente nur mit diesem proprietären, unportablen Schrott
untermauern kannst, dann geh' bitte. Seit Oracle 9i wird die ANSI-SQL
Syntax mittels LEFT OUTER JOIN unterstützt und kann dort verwendet
werden. So ziemlich alle anderen SQL-Dialekte unterstützen nur die
ANSI-Syntax.

Grüße
Kai

--
This signature is left as an exercise for the reader.

Re: Index nach OPTIMIZE wirkungslos?

On Wed, 24 Jan 2007 17:29:11 +0100, Kai Ruhnau wrote:

> Niemanden hier interessiert irgendeine sonderbare Oracle-Syntax. Wenn du
> deine Argumente nur mit diesem proprietären, unportablen Schrott
> untermauern kannst, dann geh' bitte.

Mit solchen Aussagen wäre ich ja vorsichtig. Das kann bei MySQL schnell
zurückschlagen.