Sehr langsamer Query

Hallo,

ich habe ein Performance-Problem mit einer Abfrage:

In einer Tabelle (die leider nicht vollständig normalisiert ist/werden
kann) liegen ca. 200.000 Artikel.
Nun möchte ich blätterbare Listen erstellen, die:

- nach Künstlern gruppiert und sortiert
- pro Künstler den neuesten Artikel (dessen Veröffentlichungsdatum
gekommen ist) anzeigt
- und die Ergebnisse limitiert darstellt.

Da hier recht viele Felder beteiligt sind, bin ich unschlüssig, wie ich
die Indizes anlegen soll. Momentan sieht mein Query z.B. so aus:

====

SELECT shop_products.*,
shop_fixed_prices.fixed_price
FROM shop_products

LEFT JOIN shop_fixed_prices ON (shop_fixed_prices.EAN = shop_products.EAN)

LEFT JOIN shop_products_genres ON (shop_products_genres.EAN =
shop_products.EAN)

WHERE shop_products.ID>0
AND medium_type NOT LIKE 'LP%'
AND medium_type LIKE '%CD%'
AND date_release<=NOW()

AND (shop_products.genre='Musik für Kammerensemble' OR
shop_products.kw1='Musik für Kammerensemble' OR shop_products.kw2='Musik
für Kammerensemble' OR shop_products.kw3='Musik für Kammerensemble' OR
shop_products.kw4='Musik für Kammerensemble' OR shop_products.kw5='Musik
für Kammerensemble')

AND (shop_products.cover_link !='')

GROUP BY shop_products.artist
ORDER BY shop_products.artist ASC,
shop_products.date_release DESC
====

Folgende Indizes existieren:
EAN (index)
kw (index, über Felder genre, kw1, kw2, kw3, kw4, kw5, date_release)
title (fulltext)
artist (fulltext)

Ohne "group by" und ohne Sortierung ist er blitzschnell; mit allen
Filtern braucht er quälend lange durch "using temporary" und filesort.
Ach ja, MySql liegt in Version 4.0.25 vor.

Hat jemand eine Idee?

Vielen Dank und Gruß
Stefan

--
GPG-Key-ID: 0xE8288CF8

"In the beginning the Universe was created. This has made a lot of
people very angry and has been widely regarded as a bad move."

-- Douglas Adams

Re: Sehr langsamer Query

Stefan T. Oertel schrieb:
> Hallo,
>
> ich habe ein Performance-Problem mit einer Abfrage:
>
> In einer Tabelle (die leider nicht vollständig normalisiert ist/werden
> kann) liegen ca. 200.000 Artikel.
....
>
> SELECT shop_products.*,
> shop_fixed_prices.fixed_price
> FROM shop_products
>
> LEFT JOIN shop_fixed_prices ON (shop_fixed_prices.EAN = shop_products.EAN)
>
> LEFT JOIN shop_products_genres ON (shop_products_genres.EAN =
> shop_products.EAN)
>
> WHERE shop_products.ID>0
> AND medium_type NOT LIKE 'LP%'
> AND medium_type LIKE '%CD%'
> AND date_release<=NOW()
>
> AND (shop_products.genre='Musik für Kammerensemble' OR
> shop_products.kw1='Musik für Kammerensemble' OR shop_products.kw2='Musik
> für Kammerensemble' OR shop_products.kw3='Musik für Kammerensemble' OR
> shop_products.kw4='Musik für Kammerensemble' OR shop_products.kw5='Musik
> für Kammerensemble')
>
> AND (shop_products.cover_link !='')
>
> GROUP BY shop_products.artist
> ORDER BY shop_products.artist ASC,
> shop_products.date_release DESC

1:Schick bitte immer einen Explain mit.

2:OR ist böse :-) Der Index kw bringt gar nix. Besser währe vielleicht
für die Suche nach einem Genre einen Subselect oder eine Temporäre
Tabelle mit Union-Join einzusetzten.

3: Das LIKE-gewurstel über den medium_type ist auch nicht gut.

4: ein Index über ID,medium_type,cover_link,artist,date_release hilft
vielleicht.

5: Explain posten.

6: Doch besser Normalisieren? (Wer verbricht eigentlich so ein Kaputtes
Design?)

Re: Sehr langsamer Query

On 20 Nov., 21:40, "Stefan T. Oertel" wrote:
>
> ORDER BY shop_products.artist ASC,
> shop_products.date_release DESC

Wenn Du nach date_release sortierts, solltest Du auch einen Index
drauf setzen - es sei denn date_release ist zufällig die führende
Spalte des mehrspaltigen Indizes.

Peter

Re: Sehr langsamer Query

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: Sehr langsamer Query

Peter Schleif schrieb:
> On 20 Nov., 21:40, "Stefan T. Oertel" wrote:
>> ORDER BY shop_products.artist ASC,
>> shop_products.date_release DESC
>
> Wenn Du nach date_release sortierts, solltest Du auch einen Index
> drauf setzen - es sei denn date_release ist zufällig die führende
> Spalte des mehrspaltigen Indizes.

*Ein* Index auf date_release bringt gar nichts, Da mysql sowieso nur
*einen* verwendet.
Außerdem ist date_release sowieso nur zweiter in der Sortier-Hirarchie.

Re: Sehr langsamer Query

On 21 Nov., 10:19, Harald Stowasser wrote:
>
> Außerdem ist date_release sowieso nur zweiter in der Sortier-Hirarchie.

Eben.

Je weiter hinter die zu sortierende Spalte in dem mehrspaltigen Index
steht, desto ineffektiver ist die Beschleunigung. Deshalb einen
zusätzlichen Index nur auf date_release.

Peter

Re: Sehr langsamer Query

Peter Schleif schrieb:
> On 21 Nov., 10:19, Harald Stowasser wrote:
>> Außerdem ist date_release sowieso nur zweiter in der Sortier-Hirarchie.
>
> Eben.
>
> Je weiter hinter die zu sortierende Spalte in dem mehrspaltigen Index
> steht, desto ineffektiver ist die Beschleunigung. Deshalb einen
> zusätzlichen Index nur auf date_release.

Der bringt rein gar *NICHTS*. Da Mysql nur *einen* Index nutzen kann.
Der zu benutzte Index wird jedoch von der Where-Klausel und den
Kardinalitäten bestimmt.


In manchen Fällen kann MySQL Indizes nicht zur Auflösung von ORDER BY
verwenden, obwohl es mithilfe der Indizes Datensätze findet, die der
WHERE-Klausel entsprechen. Dies betrifft u. a. die folgenden Fälle:
.....
Der Schlüssel, der zum Holen der Datensätze verwendet wird, ist nicht
derselbe wie derjenige, der in der ORDER BY-Klausel verwendet wird:

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;


Außerdem wird in vorliegender Query DESC und ASC vermischt was *immer*
einen filesort bedingt. Um Filesort zu beschleunigen hilft es ALLE
Datensätze, die der WHERE-Klausel entsprechen in einen Index zu packen.
Grundsatz:
Die Selektivste Spalte nach Links und zusätzlich die ORDER-BY spalten
nach rechts.

Genaueres zu filesort und order by:
[1]http://dev.mysql.com/doc/refman/5.1/de/order-by-optimizat ion.html

Re: Sehr langsamer Query

Andreas Kretschmer schrieb:
>> In einer Tabelle (die leider nicht vollständig normalisiert ist/werden
> Warum?

weil sie so angeliefert werden.

>> WHERE shop_products.ID>0
>> AND medium_type NOT LIKE 'LP%'
>> AND medium_type LIKE '%CD%'
>
> schlecht weil nicht sinnvoll indizierbar.
> Was für medium_typen gibt es denn, die darauf matchen?

jede Menge. Diese Typen ändern sich ständig, darum muss ich mit like
matchen.

> Normalisier Deine Daten. Alles andere führt zu nix.

Du meinst, wenn die Keywords ausgelagert wären, würde sich das ganze
erheblich beschleunigen?

Gruß Stefan

--
GPG-Key-ID: 0xE8288CF8

Re: Sehr langsamer Query

Harald Stowasser schrieb:

> Außerdem wird in vorliegender Query DESC und ASC vermischt was *immer*
> einen filesort bedingt. Um Filesort zu beschleunigen hilft es ALLE
> Datensätze, die der WHERE-Klausel entsprechen in einen Index zu packen.
> Grundsatz:
> Die Selektivste Spalte nach Links und zusätzlich die ORDER-BY spalten
> nach rechts.

ok, das probiere ich mal aus, danke.

Gruß Stefan


--
GPG-Key-ID: 0xE8288CF8

"Everything starts somewhere, although many physicists disagree"

-- Terry Pratchett

Re: Sehr langsamer Query

Stefan T. Oertel schrieb:
> Andreas Kretschmer schrieb:
>>> In einer Tabelle (die leider nicht vollständig normalisiert ist/werden
>> Warum?
>
> weil sie so angeliefert werden.

Am besten du parst den misst und schmeißt in in eine eigene aufgeräumte
Datenbank.

>>> WHERE shop_products.ID>0
>>> AND medium_type NOT LIKE 'LP%'
>>> AND medium_type LIKE '%CD%'
>> schlecht weil nicht sinnvoll indizierbar.
>> Was für medium_typen gibt es denn, die darauf matchen?
>
> jede Menge. Diese Typen ändern sich ständig, darum muss ich mit like
> matchen.

Das Problem an Like ist das '%foo%' gar keinen index benutzt, und 'foo%'
immerhin einen index /zerbricht/ d.h. alles was im index in
darauffolgenden spalten kommen würde, nicht

>> Normalisier Deine Daten. Alles andere führt zu nix.
>
> Du meinst, wenn die Keywords ausgelagert wären, würde sich das ganze
> erheblich beschleunigen?

Ja. Es gibt 2 konkurierende Methoden bei und-Verknüpfungen in einer M:N
Beziehung. Trotzdem ist das schneller als LIKE gewurstel.

1: Mit Having.
2: Mit X-Join.

Vor und Nachteile dazu wurden hier schon oft diskutiert:

opr8nufmu7mddsn6 at news.individual.de

Re: Sehr langsamer Query

Stefan T. Oertel schrieb:
> Andreas Kretschmer schrieb:
>>> In einer Tabelle (die leider nicht vollständig normalisiert
>>> ist/werden
>> Warum?
>
> weil sie so angeliefert werden.

Am besten du parst den misst und schmeißt in in eine eigene aufgeräumte
Datenbank.

>>> WHERE shop_products.ID>0 AND medium_type NOT LIKE 'LP%' AND
>>> medium_type LIKE '%CD%'
>> schlecht weil nicht sinnvoll indizierbar. Was für medium_typen gibt
>> es denn, die darauf matchen?
>
> jede Menge. Diese Typen ändern sich ständig, darum muss ich mit like
> matchen.

Das Problem an Like ist das '%foo%' gar keinen index benutzt, und 'foo%'
immerhin einen index /zerbricht/ d.h. alles was im index in
darauffolgenden spalten kommt benötigt einen scann.

>> Normalisier Deine Daten. Alles andere führt zu nix.
>
> Du meinst, wenn die Keywords ausgelagert wären, würde sich das ganze
> erheblich beschleunigen?

Ja. Es gibt 2 konkurierende Methoden bei und-Verknüpfungen in einer M:N
Beziehung. Trotzdem ist das schneller als LIKE gewurstel.

1: Mit Having.
2: Mit X-Join.

Vor und Nachteile dazu wurden hier schon oft diskutiert:

opr8nufmu7mddsn6 at news.individual.de

Re: Sehr langsamer Query

Harald Stowasser schrieb:
>> Du meinst, wenn die Keywords ausgelagert wären, würde sich das ganze
>> erheblich beschleunigen?
>
> Ja. Es gibt 2 konkurierende Methoden bei und-Verknüpfungen in einer M:N
> Beziehung. Trotzdem ist das schneller als LIKE gewurstel.
>
> 1: Mit Having.
> 2: Mit X-Join.

ich bekomme die Daten jetzt mit normalisierten Keywords (und frage per
inner join ab)- das war tatsächlich der Flaschenhals. Ich war schon
davon ausgegangen, dass das Gruppieren und zweimalige Sortieren das
Zeitaufwendige sei. Wieder etwas gelernt.

Vielen Dank,
Stefan

--
GPG-Key-ID: 0xE8288CF8