slow_query_log, rows_examined

Hallo,

wir haben das slow_query_log eingeschaltet und folgende Frage dazu:

Tabelle
--------------------------------------
test
- test_id INT(10)
- test_ident VARCHAR(10)
- test_timestamp INT(10)
- test_status INT(10)
- test_xml TEXT

Index
--------------------------------------
test_ident(5), test_timestamp, test_status


SQL-Statement
--------------------------------------
SELECT test_id FROM test WHERE test_ident='mario' AND ((test_timestamp
IS NULL OR test_timestamp=0) OR (test_timestamp<1164644668 AND
test_status=1))


In der Tabelle sind 70000 Einträge enthalten.
Wenn ich das SQL-Statement sende, dann steht in der slow_query_log, dass
70.000 rows "examined" wurden.
Bedeutet das "examine" nicht, dass das die "übrigen" Datensätze sind,
die nach Anwendung der WHERE-Klauseln übrig bleiben?
Z. B.
SELECT id from tabelle WHERE id<=10
-> müsste rows_examined = 10 ergeben?

Funktioniert das ganze Prozedere nicht, wenn man mit Indizes auf
VARCHAR-Spalten geht?

Das oben angegebene Statement gibt dann nur 34 Ergebnisse zurück und
braucht über 50 Sekunden!
Wenn ich ein SELECT COUNT(*) FROM ... mache, dann kommt das Ergebnis in
0,1 Sekunde zurück. Nur wenn ich ein Feld mit angebe, dann dauert es
sehr lange.

Woran kann das liegen?

Vielen Dank für Tipps und Hinweise!



Grüße

M. Schröter

--
PS: Aus der Absender-E-Mail-Adresse muss das ".ng" entfernt werden, wenn
man mir via E-Mail antworten möchte.

Re: slow_query_log, rows_examined

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: slow_query_log, rows_examined

Mario Schröter wrote:

> wir haben das slow_query_log eingeschaltet und folgende Frage dazu:
^^^
Pluralis Majestatis?

> Tabelle
> --------------------------------------
> test
> - test_id INT(10)
> - test_ident VARCHAR(10)
> - test_timestamp INT(10)
> - test_status INT(10)
> - test_xml TEXT
>
> Index
> --------------------------------------
> test_ident(5), test_timestamp, test_status

Örks. Bitte poste hier den exakten Tabellenaufbau wie ihn dir z.B. SHOW
CREATE TABLE anzeigt.

Verstehe ich das richtig, daß von der test_ident Spalte nur die ersten
5 Zeichen indiziert sind? Warum nicht die ganze Spalte?

> In der Tabelle sind 70000 Einträge enthalten.

Und wieviele davon haben in den ersten 5 Zeichen von test_ident den
Wert 'mario'?

> SELECT test_id FROM test WHERE test_ident='mario' AND ((test_timestamp
> IS NULL OR test_timestamp=0) OR (test_timestamp<1164644668 AND
> test_status=1))

> Wenn ich das SQL-Statement sende, dann steht in der slow_query_log, dass
> 70.000 rows "examined" wurden.
> Bedeutet das "examine" nicht, dass das die "übrigen" Datensätze sind,
> die nach Anwendung der WHERE-Klauseln übrig bleiben?

Nein, es ist die Anzahl der Datensätze, die "untersucht" (examined)
wurden um zu testen, ob die WHERE Bedingungen auf sie zutreffen.

Wenn diese Zahl gleich der Anzahl der Zeilen in der Tabelle ist, heißt
das daß ein full table scan gemacht werden muß. In anderen Worten: es
wird kein Index verwendet. EXPLAIN zeigt dir Details.

> Z. B.
> SELECT id from tabelle WHERE id<=10
> -> müsste rows_examined = 10 ergeben?

Im allgemeinen Fall: Nein. Wenn ein UNIQUE Index auf (id) liegt, dann
vielleicht. Aber auch dann muß der Optimizer den Index nicht verwenden.
Wenn die Tabelle z.B. nur 5 Zeilen hat, ist ein table scan schneller
als ein Indexzugriff.

> Funktioniert das ganze Prozedere nicht, wenn man mit Indizes auf
> VARCHAR-Spalten geht?

Kommt auf deine Definition von "funktionieren" an. Wenn ein Index nicht
selektiv ist, dann wird er nicht verwendet. Aber die Optimierung von
Queries und Schema ist eigentlich der interressante (sprich schwierige)
Teil des Designs von datenbankgestützten Applikationen.

> Das oben angegebene Statement gibt dann nur 34 Ergebnisse zurück und
> braucht über 50 Sekunden!

Wenn es einen Table Scan über 70.000 Records machen muß und da evtl.
auch noch große XML-Brocken dabei sind, ist das nicht verwunderlich.

> Wenn ich ein SELECT COUNT(*) FROM ... mache, dann kommt das Ergebnis in
> 0,1 Sekunde zurück.

Und? Wenn du jemand fragst, wieviele Zahlen mit 5 Dezimalstellen es
gibt, kann er dir das auch schneller sagen als wenn du nach einer Liste
dieser Zahlen fragst, die eine 3 an erster Stelle haben und eine
Quersumme zwischen 25 und 42.


XL

Re: slow_query_log, rows_examined

Axel Schwenke schrieb:

> Mario Schröter wrote:
>
>
>>wir haben das slow_query_log eingeschaltet und folgende Frage dazu:
>
> ^^^
> Pluralis Majestatis?

Ja, wir sitzen grad zu zweit an dem Problem. ;-)


> Örks. Bitte poste hier den exakten Tabellenaufbau wie ihn dir z.B. SHOW
> CREATE TABLE anzeigt.

CREATE TABLE `test` (
`test_id` int(10) unsigned NOT NULL auto_increment,
`test_ident` varchar(10) NOT NULL,
`test_timestamp` int(10) unsigned NOT NULL,
`test_status` int(10) unsigned NOT NULL,
`test_xml` text NOT NULL,
PRIMARY KEY (`test_id`),
UNIQUE KEY `test_id` (`test_id`),
KEY `test_id_2` (`test_id`),
KEY `NewIndex` (`test_ident`,`test_timestamp`,`test_status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Seltsamer Weise sehe ich nicht in dem SQL-Statement, dass test_ident mit
5 Zeichen Länge indiziert wurde, obwohl ich beim Index-Befehl 5 Zeichen
angegeben habe.

Was ich vergessen habe, anzugeben: Wir benutzen MySQL 4.1.20-com unter
Windows 2003 Server (bitte keine Grundsatzdiskussion *g*).


> Verstehe ich das richtig, daß von der test_ident Spalte nur die ersten
> 5 Zeichen indiziert sind? Warum nicht die ganze Spalte?

Hm, weil sich die Identifier nur in den ersten 5 Zeichen unterscheiden. :-)


>>In der Tabelle sind 70000 Einträge enthalten.
>
>
> Und wieviele davon haben in den ersten 5 Zeichen von test_ident den
> Wert 'mario'?

69.500


>>SELECT test_id FROM test WHERE test_ident='mario' AND ((test_timestamp
>>IS NULL OR test_timestamp=0) OR (test_timestamp<1164644668 AND
>>test_status=1))
>
>
>>Wenn ich das SQL-Statement sende, dann steht in der slow_query_log, dass
>>70.000 rows "examined" wurden.
>>Bedeutet das "examine" nicht, dass das die "übrigen" Datensätze sind,
>>die nach Anwendung der WHERE-Klauseln übrig bleiben?
>
>
> Nein, es ist die Anzahl der Datensätze, die "untersucht" (examined)
> wurden um zu testen, ob die WHERE Bedingungen auf sie zutreffen.
>
> Wenn diese Zahl gleich der Anzahl der Zeilen in der Tabelle ist, heißt
> das daß ein full table scan gemacht werden muß. In anderen Worten: es
> wird kein Index verwendet. EXPLAIN zeigt dir Details.

Mit Explain haben wir schon geguckt. Dort sagt er, dass er den Index
"NewIndex" benutzt. Deswegen wundere ich mich ja, dass das keinen
Geschwindigkeitsvorteil bringt. Hätte er keinen Index benutzt, dann wäre
mir alles klar gewesen. :-/



Grüße

Mario & Tino

--
PS: Aus der Absender-E-Mail-Adresse muss das ".ng" entfernt
werden, wenn man mir via E-Mail antworten möchte.

Re: slow_query_log, rows_examined

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: slow_query_log, rows_examined

=?ISO-8859-1?Q?Mario_Schröter?= wrote:
> Axel Schwenke schrieb:
>
>> Bitte poste hier den exakten Tabellenaufbau wie ihn dir z.B. SHOW
>> CREATE TABLE anzeigt.
>
> CREATE TABLE `test` (
> `test_id` int(10) unsigned NOT NULL auto_increment,
> `test_ident` varchar(10) NOT NULL,
> `test_timestamp` int(10) unsigned NOT NULL,
> `test_status` int(10) unsigned NOT NULL,
> `test_xml` text NOT NULL,
> PRIMARY KEY (`test_id`),
> UNIQUE KEY `test_id` (`test_id`),
überflüssig
> KEY `test_id_2` (`test_id`),
dito
> KEY `NewIndex` (`test_ident`,`test_timestamp`,`test_status`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1

>> Verstehe ich das richtig, daß von der test_ident Spalte nur die ersten
>> 5 Zeichen indiziert sind? Warum nicht die ganze Spalte?
>
> Hm, weil sich die Identifier nur in den ersten 5 Zeichen unterscheiden. :-)

Wozu speicherst du dann 10 Zeichen?

>>>In der Tabelle sind 70000 Einträge enthalten.
>>
>> Und wieviele davon haben in den ersten 5 Zeichen von test_ident den
>> Wert 'mario'?
>
> 69.500

Das macht einen Index auf test_ident wenig selektiv, zumindest wenn
man nach dem Wert 'mario' sucht.

>>>SELECT test_id FROM test WHERE test_ident='mario' AND ((test_timestamp
>>>IS NULL OR test_timestamp=0) OR (test_timestamp<1164644668 AND
>>>test_status=1))

>> ... daß ein full table scan gemacht werden muß. In anderen Worten: es
>> wird kein Index verwendet. EXPLAIN zeigt dir Details.
>
> Mit Explain haben wir schon geguckt. Dort sagt er, dass er den Index
> "NewIndex" benutzt.

Jein. Es wird wahrscheinlich ein Prefix von NewIndex benutzt. Nämlich
genau der Teil, der die test_ident Spalte indiziert. Den Rest des Index
kann dein leicht angestaubtes MySQL 4.1.20 nicht verwenden, weil da ein
OR im WHERE vorkommt. Mit 5.0 sollte das besser aussehen. Wenn du bei
4.1 bleiben willst, solltest du zwei Queries machen und die Ergebnisse
per UNION wieder zusammenführen.

Dummerweise ist dieser Teil des Index nicht selektiv, so daß ein
Lookup nichts bringt. Allerdings sind die restlichen Spalten aus dem
WHERE im Index und es wird ein full index scan. Das ist zwar schon mal
besser als ein Tablescan, aber nicht *viel* besser. Allerdings würde
ein etwas größerer key_buffer (in my.ini einzustellen) vermutlich viel
bringen. Da du Windows sagst, vermute ich stark, daß die my.ini noch
die Defaults enthält. Die sind sehr konservativ.


XL

Re: slow_query_log, rows_examined

Axel Schwenke schrieb:

> Mario Schröter wrote:
>
>
>>Das oben angegebene Statement gibt dann nur 34 Ergebnisse zurück und
>>braucht über 50 Sekunden!
>
>
> Wenn es einen Table Scan über 70.000 Records machen muß und da evtl.
> auch noch große XML-Brocken dabei sind, ist das nicht verwunderlich.
>

Ein

select sum(LENGTH(test_xml)) from test where test_status=0

ergibt "946738470" ... könnte es daran liegen? ;-)

Aber warum bremst die Spalte "test_xml" die SELECT's aus, die gar nicht
auf diese Spalte gehen?


Grüße

Mario

--
PS: Aus der Absender-E-Mail-Adresse muss das ".ng" entfernt
werden, wenn man mir via E-Mail antworten möchte.

Re: slow_query_log, rows_examined

Mario Schröter wrote:
> PRIMARY KEY (`test_id`),
> UNIQUE KEY `test_id` (`test_id`),
> KEY `test_id_2` (`test_id`),

Die unteren beiden kannst Du uebrigens kicken, die bringen keinen
zusaetzlichen Nutzen, sondern machen im Zweifelsfall nur Updates
teurer.

Re: slow_query_log, rows_examined

=?ISO-8859-1?Q?Mario_Schröter?= wrote:
> Axel Schwenke schrieb:
>> Mario Schröter wrote:
>>
>>>Das oben angegebene Statement gibt dann nur 34 Ergebnisse zurück und
>>>braucht über 50 Sekunden!
>>
>> Wenn es einen Table Scan über 70.000 Records machen muß und da evtl.
>> auch noch große XML-Brocken dabei sind, ist das nicht verwunderlich.
>
> select sum(LENGTH(test_xml)) from test where test_status=0
>
> ergibt "946738470" ... könnte es daran liegen? ;-)
>
> Aber warum bremst die Spalte "test_xml" die SELECT's aus, die gar nicht
> auf diese Spalte gehen?

Weil ein *Table* Scan das gesamte Datenfile liest, also komplette
Records incl. eigentlich nicht benötigter Spalten. Ein Index Scan
braucht das nicht, der liest statt dessen einen kompletten Index
(aber auch incl. eigentlich nichtbenötigter Spalten - wenn denn
solche im Index sind). Was *genau* MySQL da tut, sagt EXPLAIN in
der Spalte 'type'. Da du diese Information bisher nicht mit uns
teilst, kann ich dazu nix sagen...


XL