SQL_CALC_FOUND_ROWS() (Datenbanken und Verzeichnisdienste)

[thread]3810[/thread]

SQL_CALC_FOUND_ROWS()

Froschpopo

2007-05-13 21:14

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Ich mal wieder mit Problemen

Code: (dl )

SELECT
    SQL_CALC_FOUND_ROWS
    a.id,
    a.name,
    a.birthday,
    b.id

FROM defaultusers AS a

LEFT JOIN photos AS b ON
    b.id = (SELECT MAX(id) FROM photos WHERE user_id = a.id)

WHERE
    a.status = 'N' and
    a.sex = 'w'

ORDER BY a.lastlogin DESC
LIMIT 0, 10;

Das Problem: ohne SQL_CALC_FOUND_ROWS() dauert die Abfrage 0.13 Sekunden und das bei über 400.000 Bildern (LongBlob) in "photos". Es ist also eine richtig große Datenbank.
Aber sobald ich SQL_CALC_FOUND_ROWS() einsetze, dauert die Abfrage bis zu 16 Sekunden!
Es muss etwas mit dem JOIN zu tun haben, denn ohne JOIN ist SQL_CALC_FOUND_ROWS() extrem schnell.
Das komische ist aber, dass ohne SQL_CALC_FOUND_ROWS() der JOIN sehr schnell ist!
Woran bin ich denn nun?

Das sagt EXPLAIN:

Code: (dl )

+----+--------------------+--------+--------+---------------+------------+---------+------

------+-------+-----------------------------+
| id | select_type        | table  | type   | possible_keys | key        | key_len | ref        | rows  | Extra                       |
+----+--------------------+--------+--------+---------------+------------+---------+------

------+-------+-----------------------------+
|  1 | PRIMARY            | a      | ALL    | NULL          | NULL       |    NULL | NULL       | 20531 | Using where; Using filesort |
|  1 | PRIMARY            | b      | eq_ref | PRIMARY       | PRIMARY    |       4 | func       |     1 | Using index                 |
|  2 | DEPENDENT SUBQUERY | photos | ref    | usernummer    | usernummer |       3 | users.a.id |     2 |                             |
+----+--------------------+--------+--------+---------------+------------+---------+------

------+-------+-----------------------------+
3 rows in set (0.00 sec)

Wie man sieht sind INDEX's vorhanden.\n\n

nepos

2007-05-13 21:25

User since
2005-08-17
1420 Artikel
BenutzerIn

Dein Problem liegt an der Arbeitsweise von SQL_CALC_FOUND_ROWS. Ohne dieses Schlüsselwort zieht das Limit und die Datenbank muss nicht alle 400000 Bilder durchgehen. SQL_CALC_FOUND_ROWS liefert die Anzahl der Zeilen, die deine Abfrage ergeben würde ohne die Berücksichtigung der LIMIT-Klausel. Die Datenbank muss also alle Ergebniszeilen berechnen, um die Anzahl bestimmen zu können. Drum dauerts eben so lange.

Wenn du nur die Anzahl der Ergebnisse dieses SELECTs willst, also unter Berücksichtigung des LIMIT, dann lass SQL_CALC_FOUND_ROWS weg und benutz nach dem SELECT die Funktion FOUND_ROWS().

Nachlesen kannst du das ganze hier: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html
bei der Funktion FOUND_ROWS().

Froschpopo

2007-05-13 21:37

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Was SQL_CALC_FOUND_ROWS() macht weiss ich selbst, sonst hätte ich es nicht angewadt.
Ich brauche unbedingt die Anzahl der Gesamtergebnisse damit ich später die Seiten-Zahl für die HTML-Ausgabe berechnen kann.
Ich brauche dazu aber nur die Gesamtergebnisse von der "defaultusers"-Tabelle und nicht auch noch die von "photos".

es müsste irgendwie sowas hier geben:

Code: (dl )

SELECT SQL_CALC_FOUND_ROWS(a.id)...

dann könnte man SQL_CALC_FOUND_ROWS() sagen, dass man nur das Ergebnis von a haben möchte. Das geht nämlich dann sehr schnell.

2007-05-14 01:47

User since
2003-08-04
12208 Artikel
Admin1

sagt explain was unterschiedliches bei "possible keys", mit und ohne
SQL_CALC_FOUND_ROWS?
wenn ja, kannst du den index mit force index erzwingen.
wenn nicht, versuch mal das normale select und danach ein count
ohne limit und order zu machen und guck, ob das zusammengezählt schneller ist.\n\n

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

Froschpopo

2007-05-14 08:56

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Morgen!
Das Ergebnis von EXPLAIN mit und ohne SQL_CALC_FOUND_ROWS ist haargenau dasselbe.
Das Problem ist eigentlich, dass zu dem Statement später noch zahlreiche weitere JOIN's, Sub-Select's und eine große Menge von WHERE-Bedingungen folgt.
Ich müsste dasselbe Statement dann nochmal ausführen, nur wegen einem COUNT(). Da bietet es sich schon an, SQL_CALC_FOUND_ROWS beim ersten Query schon zu implentieren, dann muss ich später nur noch das Ergebnis abgreifen. Insofern ist SQL_CALC_FOUND_ROWS genau das was ich suche!
Soll heißen: Auch ohne LIMIT und ORDER wäre die Abfrage ohnehin schon relativ mächtig.

Zu "FORCE INDEX" habe ich noch eine Frage: Welchen INDEX soll ich erzwingen?
Habs schon versucht mit

Code: (dl )

FORCE INDEX(PRIMARY)

aber das macht die Abfrage nur noch langsamer.
Mit FORCE INDEX dauert sie 18 Sekunden, ohne 12 Sekunden.

Das alles muss etwas mit der Größe von "photos" zu tun haben. Denn die Tabelle ist trotz der wenigen Spalten (4) sehr groß: über 400.000 Datensätze ergeben insg. fast 1.2GB.
Ich habe mich aber ordnungsgemäß an sämtliche Optimierungstipps gehalten und verwende auch keinen INDEX auf die LongBlob-Spalte.

hab dazu auch noch im bugreport etwas gefunden:
http://bugs.mysql.com/bug.php?id=25976
das soll angeblich kein bug sein.

So ganz versteh ich es aber noch nicht.

Ich könnt schon wieder and die Decke gehen und das um 8 Uhr in der Früh. Alles wieder scheisse hier.\n\n

2007-05-14 11:25

User since
2003-08-04
12208 Artikel
Admin1

hrm. ich sagte ja, führ mal ein count aus und guck, wie langsam oder schnell das ist.
was verstehst du denn jetzt genau nicht? warum mysql so lange braucht, 2 mehr oder
minder grosse tabellen
zu joinen und das mit einer nicht trivialen join-bedingung?
mit limit funktioniert das halt so schnell, weil mysql ziemlich gut rausfinden kann,
ab wann es die ersten X ergebnisse zusammenhat. da du nach einer spalte
von defaultusers sortierst, muss mysql eben viel weniger joinen.

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

Froschpopo

2007-05-14 11:49

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Du willst also ernsthaft, dass ich das Mörderstatement doppelt ausführe, nämlich nochmal mit COUNT(id) und ohne ORDER-BY und LIMIT:

Code: (dl )

SELECT
    SQL_CALC_FOUND_ROWS
    b.id AS file_id,
    a.id,
    a.name,
    IF(sessions.userid IS NOT NULL, '1','0') AS status

FROM defaultusers a

LEFT JOIN sessions ON
    a.id = sessions.userid

INNER JOIN photos b ON
    b.id = (SELECT MAX(datestamp) FROM photos WHERE user_id = a.id)

INNER JOIN defaultusers c ON c.id = 39212

WHERE
    a.status = 'N' and
    a.sex = 'w' and
    YEAR(CURDATE())-YEAR(a.birthday) <= 30 and
    YEAR(CURDATE())-YEAR(a.birthday) >= 16 and
    (ACOS((SIN(RADIANS(c.lat))*SIN(RADIANS(a.lat))) + (COS(RADIANS(c.lat))*COS(RADIANS(a.lat))*COS(RADIANS(a.lon)-RADIANS(c.lon)))) * 6378.388) <= 50

ORDER BY a.lastlogin DESC
LIMIT 0,10;

D.h. mein CPU müsste alles doppelt rechnen, auch den komplizierten 3D-Pytagoras... das wird übel.
Was meinst du mit nicht-trivial? Wie mache ich denn den JOIN trivial??
Man müsste SQL_CALC_FOUND_ROWS irgendwie dazu animieren, sich nur auf eine Tabelle zu beschränken.\n\n

2007-05-14 12:58

User since
2003-08-04
12208 Artikel
Admin1

[quote=Froschpopo,14.05.2007, 09:49]Du willst also ernsthaft, dass ich das Mörderstatement doppelt ausführe, nämlich nochmal mit COUNT(id) und ohne ORDER-BY und LIMIT:[/quote]
ganz genau, so langsam kapierst dus.
zum *nachschauen*, ob es genauso lange dauert. wenn ja, dann
dauert es halt so lange. da wirst du dann auch nix dran ändern können.

Quote
D.h. mein CPU müsste alles doppelt rechnen, auch den komplizierten 3D-Pytagoras... das wird übel.

ich sagte schon vorher, du sollst es mal machen und gucken, wie lange es dauert.
ich sagte nicht, bau es in den produktionscode ein. willst du nun
debuggen oder nicht?

Quote
Was meinst du mit nicht-trivial? Wie mache ich denn den JOIN trivial??

ein join, der auf den primary key geht, nenne ich trivial. du jedoch
joinst auf eine id mit einem wert, der sich bei jeder zeile ändert. kein wunder.

Quote
Man müsste SQL_CALC_FOUND_ROWS irgendwie dazu animieren, sich nur auf eine Tabelle zu beschränken.

was genau willst du denn zählen? wenn du die daten aus der anderen
tabelle für die anzahl nicht brauchst, dann lass sie halt raus.

Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live. -- Damian Conway in "Perl Best Practices"
lesen: Wiki:

Wie frage ich & perlintro Wiki:

brian's Leitfaden für jedes Perl-Problem

Froschpopo

2007-05-14 13:09

User since
2003-08-15
2653 Artikel
BenutzerIn
[default_avatar]

Quote
was genau willst du denn zählen? wenn du die daten aus der anderen
tabelle für die anzahl nicht brauchst, dann lass sie halt raus."

Ich brauche die Anzahl der Ergebnisse von defaultusers, damit ich später angeben kann, auf wieviel HTML-Seiten die Ergebnistabelle verteilt wird. Dazu muss ich das LIMIT überwinden und das geht nur mit SQL_CALC_FOUND_ROWS.

Quote
ich sagte schon vorher, du sollst es mal machen und gucken, wie lange es dauert.
ich sagte nicht, bau es in den produktionscode ein. willst du nun
debuggen oder nicht

hab ich ja gemacht. Der Code dauert mit zwei Statements fast solang, wie nur eines mit SQL_CALC_FOUND_ROWS. Es kommt fast dasselbe dabei raus.

Zum Thema trivial: Das ist nicht möglich, da ich vor dem LEFT JOIn die ID-Primärschlüssel noch nicht kenne. Den will ich ja dadurch finden.
Den Primärschlüssel finde ich über einen INDEX (user_id).
Wie gesagt: Ohne SQL_CALC_FOUND_ROWS ist auch der nicht-triviale JOIN sehr schnell (ca. 0.2 Sek bei demselben Query, also einer großen Datenmenge).\n\n

nepos

2007-05-14 13:12

User since
2005-08-17
1420 Artikel
BenutzerIn

Nen Count() sollte doch recht schnell gehen. Insbesondere, wenn du da nur eine Tabelle im Spiel hast. MySQL speichert die Anzahl der Zeilen in einer Tabelle ja irgendwo und muss dann nicht über die ganze Tabelle laufen, wie das in dem Fall PostgreSQL machen würde.
Also einfach nen

Code: (dl )

SELECT count(id) FROM defaultusers

, das ist doch, was du brauchst oder?

View all threads created 2007-05-13 21:14.