mySQL: Einträge zählen mit count - Performance ...

[Wuppi]

Hallo zusammen,

für mein privates Langzeitprojekt bin ich gerade dabei die Performance zu verbessern. Ich switche von mysql_num_rows() zu "select count(...)". Gerade bei den Statistiken mit sehr vielen Einträgen hat sich die Performance spürbar verbessert (Script-Laufzeit von 5s auf 2sek). Jetzt suche ich weitere num_rows und möchte die auch ändern. Klappt überwiegend, weil ich nicht sooo umfangreiche Abfragen habe ... bis auf eine:

Für die Pagination benötige ich die Gesamtanzahl der Einträge ... da es sich hier um Mengen bis in die 20.000 handelt, ist count() wieder der Favorit. Nach viel probieren hab ich die Lösung gefunden ... aber die Performance ist gefühlt langsamer als mit num_rows!

Ausgangslage (ALT)

Code: Alles auswählen

SELECT COUNT( ofr.persidofdb ) AS anz, ofr.persidofdb, ofr.name, ofp.geschlecht, ofp.persidofdb FROM ofdb_rollen AS ofr, ofdb_personen AS ofp WHERE ofr.funktion LIKE 'Darsteller' and ofr.persidofdb = ofp.persidofdb AND ofp.geschlecht LIKE 'm' GROUP BY ofp.persidofdb

Ergebniss:
13.500 Zeilen

Code: Alles auswählen

[ANZ | Persidofdb | Name | Geschlecht]
[22 | 123 | Herr Abc | m]
[20 | 234 | Herr Bcd | m]
[...]

Hier hab ich dann ein num_rows gemacht und wusste: 13.500 ...

Jetzt muß ich diese Abfrage erweitern um ein count() setzen zu können. Mein Ansatz bisher:

Code: Alles auswählen

SELECT count(anz) from (
SELECT 
	COUNT(ofr.persidofdb) AS anz, 
	ofr.persidofdb AS egal, 
	ofr.name, 
	ofp.geschlecht, 
	ofp.persidofdb 
FROM 
	ofdb_rollen AS ofr, 
	ofdb_personen AS ofp 
WHERE 
	ofr.funktion LIKE 'Darsteller' 
	and ofr.persidofdb = ofp.persidofdb 
	AND ofp.geschlecht LIKE 'm' 
GROUP BY 
	ofp.persidofdb
    ) AS auchegal

den alias egal mußte ich bilden da ich sonst ein duplicate column "persidofdb" bekomme ...

Der Seitenwechsel ist jetzt gefühlt langsamer (die Abfrage in phpMyAdmin dauert 1,2sek) - trotz count. Vermutlich das verschachteln von SELECT?
Was kann ich jetzt noch optimieren?

Gruß
Wuppi

[oxpus]

Sagen wir es mal so:
num_rows zählt in der Regel die Anzahl der abgefragten Datensätze, also das Ergebnis einer Select Anweisung, count hingegen führt die Datenbank selber/direkt aus.
Der Performanceunterschied kommt meist zustande, wenn count auf ein nicht indiziertes Feld zählen soll. Das dauert dann je nach Anzahl Datensätze möglicherweise länger, als die Abfrage selber und anschließender Zählung per num_rows, wenn in dem Select Where-Bedingungen auf indizierte Felder enthalten sind.
Ist also das besagte Feld indiziert, liefert Count die Anzahl aus dem Index zurück. Und das ist damit und dann schneller als num_rows.

Achtung:
Verschachtelt du Select Anweisungen, wird meist die Verwendung des Index ausgehebelt und count verhält sich dann ähnlich wie num_rows. Dazu auch mit ggf. noch höherem Performanceunterschied, da dann absolut kein Index verwendet wird, also auch im Select nicht mehr.

Kurzum:
Der Schlüssel liegt in der Verwendung eines sinnvoll eingerichteten Index auf die Schlüsselfelder. Sei es für ein Select oder bei Verwendung von count.

[gn#36]

Wenn du per GROUP BY irgend eine Spalte zum Gruppieren auswählst und anschließend die Zahl der Einträge zählen willst, dann könnte auch sowas wie count(DISTINCT spalte) helfen. Die Gruppierung sorgt ja dafür, dass im Ergebnis effektiv jeder Eintrag in spalte nur ein mal vorkommt. Wenn du dann mit WHERE noch einschränkst, welche Zeilen dich überhaupt interessieren kannst du auf jeden Fall schon mal das Subquery vermeiden was eine ziemliche Beschleunigung darstellen sollte, außerdem kommt das Ergebnis mit etwas Glück aus dem Index.

Also insgesamt in deinem Fall grob so:

Code: Alles auswählen

SELECT count(DISTINCT ofr.persidofdb) as anz FROM tabelle ofr WHERE bedingung

Da du aus beiden Tabellen mit der ID vermutlich eine n:1 Beziehung hast (n Rollen für eine Person, aber nicht die selbe Rolle für mehrere Personen), tut es vermutlich auch ein LEFT JOIN statt beide Tabellen komplett zu überlagern und dann per WHERE zu sieben, das dürfte bei großen Tabellen auch noch mal ordentlich Zeit sparen.

[Wuppi]

Hallo zusammen,

danke euch beiden schon mal. Einen Fehler schon korrigiert ... es fehlte ein INDEX. Brachte 0,2sek mehr Performance

Trotzdem drehe ich mich gerade ...

Code: Alles auswählen

SELECT 
	COUNT(DISTINCT ofr.persidofdb) AS anz 
FROM 
	ofdb_rollen AS ofr 
	LEFT JOIN ofdb_personen AS ofp ON (ofr.persidofdb = ofp.persidofdb) 
WHERE 
	ofr.funktion LIKE 'Darsteller' 
	AND ofr.persidofdb = ofp.persidofdb 
	AND ofp.geschlecht LIKE 'm'

=> Das query ist schon mal wesentlich übersichtlicher. Nachteil:

Lösung 1 (Eröffnungspost): 1.100 Sekunden
Diese Lösung: 1.119 Sekunden

Mist ... sieht schön aus, bringt aber so nichts.

Also hab ich das DISTINCT entfernt

=> Bringt nichts ... die Duplikate die mit Group oder Distinct gekillt werden, werden in 0,8sek mitgezählt: 51.000

Also muß ich ein GROUP BY hinzufügen:

Code: Alles auswählen

GROUP BY
   ofp.persidofdb

in 0,0060sek bekomme ich jetzt 13507 "gegroupte" Zeilen ... ansich schön ... 13507 ist das Ergebnis was ich hier in einer Zelle erwarte. Aber ich habe halt 13507 Zeilen ... die muß ich jetzt also wieder zählen.

Und hier hänge ich jetzt ;(

Baue ich ein count ein? Aber es passt nicht. Das DISTINCT lohnt sich ja nicht.

Kurz mal den Aufbau der Tabellen
ofdb_personen:
persidofdb [PRIMARY, INDEX]
Name
geschlecht
...
...
...

=> persidofdb ist UNIQUE. Pro Person darf es nur einen Datensatz geben.

ofdb_rollen:
filmidofdb
persidofdb [INDEX]
funktion
name
rollename

=> mit der Tabelle hab ich insgesamt noch ein Problem (88.000 Zeilen und viele "Pseudoduplikate" - das zu "verhindern" ist das nächste Projekt ...) ... und habe daher auf alle 5 Spalten erstmal ein UNIQUE sitzen. Auf persidofdb hab ich noch INDEX
persidofdb alleinig ist natürlich NICHT UNIQUE. Die Person 1234 Kann Darsteller, Regiesseur, nochmal Darsteller (Doppelrolle) und Drehbuch sein - pro filmid

Danke schon mal
Gruß
Wuppi

[gn#36]

Also was hast du jetzt? Sowas?

Code: Alles auswählen

SELECT
   COUNT(ofr.persidofdb) AS anz
FROM
   ofdb_rollen AS ofr
   LEFT JOIN ofdb_personen AS ofp ON (ofr.persidofdb = ofp.persidofdb)
WHERE
   ofr.funktion LIKE 'Darsteller'
   AND ofr.persidofdb = ofp.persidofdb
   AND ofp.geschlecht LIKE 'm'
GROUP BY ofr.persidofdb

Wenn die DISTINCT Lösung auch schon so langsam ist fällt mir aktuell ehrlich gesagt nichts wirklich sinnvolles mehr ein.
Noch zwei Anregungen/Ideen:

Code: Alles auswählen

SELECT sum(cntr) as result FROM (SELECT
    1 as cntr
FROM
   ofdb_rollen AS ofr
   LEFT JOIN ofdb_personen AS ofp ON (ofr.persidofdb = ofp.persidofdb)
WHERE
   ofr.funktion LIKE 'Darsteller'
   AND ofr.persidofdb = ofp.persidofdb
   AND ofp.geschlecht LIKE 'm'
GROUP BY ofr.persidofdb)

Vermutlich hast du davon aber auch nichts, weil das dann auch nichts anderes tut als mysql_num_rows, nur komplizierter.

Nummer zwei:

Code: Alles auswählen

SELECT
   COUNT(ofp.persidofdb) AS anz
FROM
   ofdb_rollen AS ofr
   LEFT JOIN ofdb_personen AS ofp ON (ofr.persidofdb = ofp.persidofdb)
WHERE
   ofr.funktion LIKE 'Darsteller'
   AND ofr.persidofdb = ofp.persidofdb
   AND ofp.geschlecht LIKE 'm'

Hier lässt du einfach in der kleineren Tabelle zählen - geht dann vermutlich schneller. Sollte aber ja das gleiche liefern. Außerdem ist das was du zählst dort der Primärschlüssel, gibt's also nur 1x von Natur aus. Das müsste DISTINCT eigentlich auch schon überflüssig machen und trotzdem nicht alles zählen.