Performance-Problem

Hallo NG,

ich habe ein Performance-Problem, dass mir zu schaffen macht. Ich habe
eine Tabelle mit ca. 1.95 Mio Datensätzen, die wie folgt ausschaut:

CREATE TABLE `shopProducts` (
`recordReference` varchar(16) NOT NULL,
`mediaKey` enum('1','2','3','4','5','6','7','8','9') NOT NULL,
`productGroup` enum('0','1','2','3','4','5','6','7','8','9') NOT NULL,
`subjectCode` mediumint(5) NOT NULL,
`bindingKey` enum('0','1','2','3','4','5','6','7','8','9') NOT NULL,
`languageCode` char(3) NOT NULL,
`buzzwords` text NOT NULL,
.....
.....
`sortTextFaktor` decimal(18,16) NOT NULL,
PRIMARY KEY (`recordReference`),
KEY `idx_browse`
(`mediaKey`,`productGroup`,`subjectCode`,`bindingKey`,`langu ageCode`,`sortTextFaktor`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Wo die "...." stehen, kommen noch weitere Felder, die aber nicht
relevant sind.

Dann habe ich z.B. folgende Abfrage:

SELECT recordReference, subjectCode
FROM shopProducts
WHERE mediaKey = '1'
AND (subjectCode != 50000 AND subjectCode != 55900 AND subjectCode !=
55990) limit 17670, 10;

Ein Explain obiger Abfrage bringt mir folgendes Ergebnis:
id : 1
select_type : SIMPLE
table type : shopProducts
possible_keys : idx_browse
key : idx_browse
key_len : 1
ref : const
rows : 700059
Extra : Using where

Dies ist nun leider sehr langsam, weil scheinbar alle 700059 Rows
gescannt werden. Ist der Index "idx_browse" falsch gesetzt? Ist die
Beispielabfrage falsch formuliert? Was kann ich anders machen, damit die
gewünschten Ergebnisse schneller da sind?


Mit freundlichen Grüssen

Stefan Gläßer
Webmaster Neubuch24

Re: Performance-Problem

Stefan Gläßer schrieb:
> ich habe ein Performance-Problem, dass mir zu schaffen macht. Ich habe
> eine Tabelle mit ca. 1.95 Mio Datensätzen, die wie folgt ausschaut:
>=20
> CREATE TABLE `shopProducts` (
> `recordReference` varchar(16) NOT NULL,
> `mediaKey` enum('1','2','3','4','5','6','7','8','9') NOT NULL,
> `productGroup` enum('0','1','2','3','4','5','6','7','8','9') NOT NULL,
> `subjectCode` mediumint(5) NOT NULL,
> `bindingKey` enum('0','1','2','3','4','5','6','7','8','9') NOT NULL,
> `languageCode` char(3) NOT NULL,
> `buzzwords` text NOT NULL,
> ....
> ....
> `sortTextFaktor` decimal(18,16) NOT NULL,
> PRIMARY KEY (`recordReference`),
> KEY `idx_browse`
> (`mediaKey`,`productGroup`,`subjectCode`,`bindingKey`,`langu ageCode`,`s=
ortTextFaktor`),
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8;

Könnte es sein, dass du Lookup-Tables und JOINs brauchst? Das ENUM-Zeug=
s=20
sieht nicht besonders leserlich aus...

> Dann habe ich z.B. folgende Abfrage:
>=20
> SELECT recordReference, subjectCode
> FROM shopProducts
> WHERE mediaKey =3D '1'
> AND (subjectCode !=3D 50000 AND subjectCode !=3D 55900 AND subjectCode =
!=3D
> 55990) limit 17670, 10;

Wieviele Datensätze sind das denn dann, auf welche dein Query passen=20
soll? Ein !=3D schließt ja nur ein paar aus, oder?

Das LIMIT sorgt dafür, dass MySQL 17670 Einträge lesen und verwerfen =

muss, ehe es dein Ergebnis zeigt. Ohne ORDER BY macht das LIMIT auch=20
wenig Sinn, da keine bestimmte Reihenfolge garantiert wird.

>=20
> Ein Explain obiger Abfrage bringt mir folgendes Ergebnis:
> id : 1
> select_type : SIMPLE
> table type : shopProducts
> possible_keys : idx_browse =09
> key : idx_browse
> key_len : 1
> ref : const
> rows : 700059
> Extra : Using where
>=20
> Dies ist nun leider sehr langsam, weil scheinbar alle 700059 Rows
> gescannt werden. Ist der Index "idx_browse" falsch gesetzt?=20

Kommt drauf an, wie du ihn setzen wolltest ;)

Derzeit greift er nur, wenn du=20
`mediaKey`,`productGroup`,`subjectCode`,`bindingKey`,`langua geCode`,`sort=
TextFaktor`=20
in genau dieser Kombination abfragst. Er greift also nicht, wenn du=20
keine WHERE-Bedingung auf mediaKey hast.
Er greift nur bedingt, wenn du zwar mediaKey einschränkst, nicht aber=20
die productGroup.

Ich glaube du möchtest gern mehrere Indexe anlegen.

--=20
HTH,
Fabian

Re: Performance-Problem

Hallo Fabian,

> Könnte es sein, dass du Lookup-Tables und JOINs brauchst? Das ENUM-Zeugs
> sieht nicht besonders leserlich aus...

Naja, ich weiss was damit gemeint ist. Zusätzliche Joins wollte ich
eigentlich vermeiden.

>> Dann habe ich z.B. folgende Abfrage:
>>
>> SELECT recordReference, subjectCode
>> FROM shopProducts
>> WHERE mediaKey = '1'
>> AND (subjectCode != 50000 AND subjectCode != 55900 AND subjectCode !=
>> 55990) limit 17670, 10;

> Wieviele Datensätze sind das denn dann, auf welche dein Query passen
> soll? Ein != schließt ja nur ein paar aus, oder?

Passen tut diese Abfrage auf 566.017 Datensätze.

> Das LIMIT sorgt dafür, dass MySQL 17670 Einträge lesen und verwerfen
> muss, ehe es dein Ergebnis zeigt. Ohne ORDER BY macht das LIMIT auch
> wenig Sinn, da keine bestimmte Reihenfolge garantiert wird.

Ein zusätzliches ORDER BY erzeugt doch aber nur noch zusätzliche Arbeit
bzw. Rechenzeit, oder nicht?

> Kommt drauf an, wie du ihn setzen wolltest ;)

> Derzeit greift er nur, wenn du
> `mediaKey`,`productGroup`,`subjectCode`,`bindingKey`,`langua geCode`,`sortTextFaktor`
> in genau dieser Kombination abfragst. Er greift also nicht, wenn du
> keine WHERE-Bedingung auf mediaKey hast.
> Er greift nur bedingt, wenn du zwar mediaKey einschränkst, nicht aber
> die productGroup.

Mal ein anderes Beispiel, weshalb ich diesen Index angelegt habe:

SELECT recordReference, subjectCode
FROM shopProducts
WHERE mediaKey = '1'
AND productGroup = '1'
AND subjectCode = 11110
AND bindingKey = '5'
AND languageCode = 'ger'

Explain liefert:
id : 1
select_type : SIMPLE
table type : shopProducts
possible_keys : idx_browse
key : idx_browse
key_len : 15
ref : const,const,const,const,const
rows : 8898
Extra : Using where

Gefunden werden 7.726 Datensätze.

> Ich glaube du möchtest gern mehrere Indexe anlegen.

Ja ich glaub auch, aber welche wäre am besten? Pro Abfrage wird ja imho
immer nur ein Index verwendet.


Gruß,
Stefan

Re: Performance-Problem

Stefan Gläßer schrieb:
> Hallo Fabian,
>
>> Könnte es sein, dass du Lookup-Tables und JOINs brauchst? Das ENUM-Zeugs
>> sieht nicht besonders leserlich aus...
>
> Naja, ich weiss was damit gemeint ist. Zusätzliche Joins wollte ich
> eigentlich vermeiden.
>

Warum? Dein Modell ist schlecht leserlich, also schlecht verständlich,
also schlecht wartbar. Wer soll in drei Monaten noch wissen, was ein
mediakey=1 bedeutet?


>>> Dann habe ich z.B. folgende Abfrage:
>>>
>>> SELECT recordReference, subjectCode
>>> FROM shopProducts
>>> WHERE mediaKey = '1'
>>> AND (subjectCode != 50000 AND subjectCode != 55900 AND subjectCode !=
>>> 55990) limit 17670, 10;
>
>> Wieviele Datensätze sind das denn dann, auf welche dein Query passen
>> soll? Ein != schließt ja nur ein paar aus, oder?
>
> Passen tut diese Abfrage auf 566.017 Datensätze.
>
>> Das LIMIT sorgt dafür, dass MySQL 17670 Einträge lesen und verwerfen
>> muss, ehe es dein Ergebnis zeigt. Ohne ORDER BY macht das LIMIT auch
>> wenig Sinn, da keine bestimmte Reihenfolge garantiert wird.
>
> Ein zusätzliches ORDER BY erzeugt doch aber nur noch zusätzliche Arbeit
> bzw. Rechenzeit, oder nicht?
>

Möchtest Du ein *Problem* lösen oder möchtest Du Zeit sparen? Im
letzteren Fall ist es am schnellsten, gar keine Daten abzufragen.

Fabian hat doch schon alles Relevante dazu gesagt: Ein Limit ist
zweckfrei ohne Order, da dann die Reihenfolge der Datensätze zufällig
ist - Du *weißt* also nicht, welcher gerade die Nr. 17670 hat.

>> Kommt drauf an, wie du ihn setzen wolltest ;)
>
>> Derzeit greift er nur, wenn du
>> `mediaKey`,`productGroup`,`subjectCode`,`bindingKey`,`langua geCode`,`sortTextFaktor`
>> in genau dieser Kombination abfragst. Er greift also nicht, wenn du
>> keine WHERE-Bedingung auf mediaKey hast.
>> Er greift nur bedingt, wenn du zwar mediaKey einschränkst, nicht aber
>> die productGroup.
>
> Mal ein anderes Beispiel, weshalb ich diesen Index angelegt habe:
>
> SELECT recordReference, subjectCode
> FROM shopProducts
> WHERE mediaKey = '1'
> AND productGroup = '1'
> AND subjectCode = 11110
> AND bindingKey = '5'
> AND languageCode = 'ger'
>
> Explain liefert:
> id : 1
> select_type : SIMPLE
> table type : shopProducts
> possible_keys : idx_browse
> key : idx_browse
> key_len : 15
> ref : const,const,const,const,const
> rows : 8898
> Extra : Using where
>
> Gefunden werden 7.726 Datensätze.
>
>> Ich glaube du möchtest gern mehrere Indexe anlegen.
>
> Ja ich glaub auch, aber welche wäre am besten? Pro Abfrage wird ja imho
> immer nur ein Index verwendet.
>
Zurzeit - ja. Ich glaube mich allerdings erinnern zu können, dass sich
daran etwas ändern wird (in 5.1?). Egal.

Langsamer als jetzt kann Deine Abfrage kaum werden. Wenn die zweite
von Dir angeführt Abfrage häufig vorkommt, dann behalte einfach deinen
kombinierten Index. Und setze außerdem auf jede einzelne Spalte auch
noch einen. MySQL sucht sich dann den 'besten' raus (oder sollte das
zumindest tun), und das sollte die erste Abfrage beschleunigen.

Re: Performance-Problem

Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Deutsche PostgreSQL User Group: http://pgug.de

Re: Performance-Problem

Stefan Gläßer schrieb:

> SELECT recordReference, subjectCode
> FROM shopProducts
> WHERE mediaKey = '1'
> AND (subjectCode != 50000 AND subjectCode != 55900 AND subjectCode !=
> 55990) limit 17670, 10;

Versuch mal einen zweiten Index über:
mediaKey,subjectCode,recordReference
In genau dieser Reihenfolge!
Dann sollte es schneller werden. Im Explain sollte ein 'Extra :Using
index' stehen, weil auch der Wert von recordReference nicht von der
Platte gekratzt werden muss!

Re: Performance-Problem

Christian Kirsch wrote:
> Stefan Gläßer schrieb:
>> Ja ich glaub auch, aber welche wäre am besten? Pro Abfrage wird ja imho
>> immer nur ein Index verwendet.
>>
> Zurzeit - ja. Ich glaube mich allerdings erinnern zu können, dass sich
> daran etwas ändern wird (in 5.1?). Egal.

Der Vollständigkeit halber (und gegen das unqualifizierte Gerede von
Andreas)

"Index Merge Optimization" ist mit 5.0 GA.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat ion.html


Note: If you have upgraded from a previous version of MySQL, you should
be aware that this type of join optimization is first introduced in
MySQL 5.0, and represents a significant change in behavior with regard
to indexes. (Formerly, MySQL was able to use at most only one index for
each referenced table.)


Grüße
Kai

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

Re: Performance-Problem

Hallo Kai,

> http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimizat ion.html
>
>
> Note: If you have upgraded from a previous version of MySQL, you should
> be aware that this type of join optimization is first introduced in
> MySQL 5.0, and represents a significant change in behavior with regard
> to indexes. (Formerly, MySQL was able to use at most only one index for
> each referenced table.)
>

Heisst dass jetzt soviel, dass ab 5.0 einzelne Indizes auf jeder Spalte
je nach Bedarf zusammengemixt werden? D.h., wenn ich in meinem Falle
einzelne Indexe auf jede Spalte setze? Toll wäre das auf jeden Fall!


Gruß,
Stefan

Re: Performance-Problem

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: Performance-Problem

Harald Stowasser schrieb:
> Versuch mal einen zweiten Index über:
> mediaKey,subjectCode,recordReference
> In genau dieser Reihenfolge!

Die Reihenfolge wär noch ein Ansatz, passt hier aber wahrscheinlich: der
Mehrfachschlüssel funktioniert nach meinem Wissen besser wenn zuerst das
Kriterium gewählt wird, für das es viele Einträge gibt, und wo das
Kriterium gleich mal sehr viele ausschliesst; wahrscheinlich der mediaKey.

Und natürlich kann man auch "subjectCode not in (....)" formulieren, ich
aber keine Speed-differenz vermute.

M

Re: Performance-Problem

Michael Steinboeck schrieb:

> Harald Stowasser schrieb:
>
>> Versuch mal einen zweiten Index über:
>> mediaKey,subjectCode,recordReference
>> In genau dieser Reihenfolge!
>
>
> Die Reihenfolge wär noch ein Ansatz, passt hier aber wahrscheinlich: der
> Mehrfachschlüssel funktioniert nach meinem Wissen besser wenn zuerst das
> Kriterium gewählt wird, für das es viele Einträge gibt, und wo das
> Kriterium gleich mal sehr viele ausschliesst; wahrscheinlich der mediaKey.

Ja.
Abfrage passt auf: 566.017 Datensätze.
Da die Tabelle 1.950.000 Datensätze besitzt, und der Explain nur bei der
Verwendung von idx_browse 700.059 Datensätze *schätzt* habe ich mediaKey
als den selektiveren *angenommen*:

(1.950.000/700.059 = 2,78
700.059/566.017 = 1,24)

Re: Performance-Problem

Andreas Kretschmer schrieb:
[...]
> Limitation von MySQL. Du kannst aber dennoch mehrere anlegen und wild
> darauf spekulieren, daß MySQL schon einen passenden wählen wird und
> vielleicht in (weiter) Zukunft diese Limitation fällt...

Seit wievielen Jahren hat denn Postgres dieses Feature schon?

Stefan

Re: Performance-Problem

Hi Stefan

Weinzierl Stefan wrote:
> Andreas Kretschmer schrieb:
>> Limitation von MySQL. Du kannst aber dennoch mehrere anlegen und wild
>> darauf spekulieren, daß MySQL schon einen passenden wählen wird und
>> vielleicht in (weiter) Zukunft diese Limitation fällt...
> Seit wievielen Jahren hat denn Postgres dieses Feature schon?

Ich bin kein Fan von Andreas' Missionierungsversuchen - aber hier steht
ja nichts davon, dass Postgres dieses Feature haben würde...

Liebe Menschheit, hört auf zu phantasieren...

SCNR, Johannes

Re: Performance-Problem

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: Performance-Problem

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: Performance-Problem

Andreas Kretschmer schrieb:
> begin Johannes Vogel wrote:
>> Hi Stefan
>
>> Weinzierl Stefan wrote:
>>> Andreas Kretschmer schrieb:
>>>> Limitation von MySQL. Du kannst aber dennoch mehrere anlegen und wild
>>>> darauf spekulieren, daß MySQL schon einen passenden wählen wird und
>>>> vielleicht in (weiter) Zukunft diese Limitation fällt...
>>> Seit wievielen Jahren hat denn Postgres dieses Feature schon?
>
>> Ich bin kein Fan von Andreas' Missionierungsversuchen - aber hier steht
>> ja nichts davon, dass Postgres dieses Feature haben würde...
>
> Das stimmt, davon steht nix da. Aber, um Dich zu beruhigen: PG kann es.
>

Seit 8.1, IIRC, oder?