✔ Rows_examined > 1 MIO

D

dennisnowack

Mitglied
Hi Leute!

Ich sitze gerade an einem Fremdprojekt, die Datenbank zu optimieren und habe hier ein Problem mit einer Tabelle. Die Abfrage lautet in etwa so:

SELECT * FROM tabelle WHERE id > '0' AND ( wert = '0' OR wert = '1000007202') AND NOW() < DATE_ADD( dateTime, INTERVAL 24 HOUR ) ORDER BY id DESC LIMIT 10

Die Tabelle hat unter anderem die Spalten:
id (primary key)
wert (von mir testweise als index gesetzt)
dateTime (von mir ebenso testweise als index gesetzt)

Die Abfrage selbst sollte "lediglich" um die 6.000 rows als Ergebnis ausspucken, limitiert auf 10 ^^
Daher dachte / denke ich mir, dass es auch möglich sein müsste/sollte, die Tabelle so zu optimieren, dass die EXPLAIN Abfrage nur noch die etwaigen 6.000 rows ausspuckt statt die über 1,2 MIO.

die EXPLAIN abfrage spuckt derzeit folgendes aus:
id |select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | tabelle | range | PRIMARY,channel | channel | 4 | NULL | 1247214 | Using where; Using filesort
ich habe auch schon versucht einen kombinierten Index zu setzen, was bei anderen Tabellen / Abfragen Wunder bewirkte, aber ich bekomme diese Tabelle / Abfrage par tout nicht optimiert ...

Hat jemand einen Lösungsansatz

Danke im Voraus ^^
 
Was für eine Datenbank?

Nachtrag:
Versuch es mal mit Unterabfragen, damit er ev. zuerst filtert. Die Unterabfrage sollte den stärksten Filter beinhalten (also das was am meisten % Der Daten ausfiltert).

Beispiel falls ( wert = '0' OR wert = '1000007202') den grössten Filter ausmacht:
Am besten mit einem Index auf wert
SQL: 
SELECT 
	* 
FROM 
	(SELECT *
	FROM 	tabelle 
	WHERE	wert = '0' OR wert = '1000007202') AS t
WHERE 
	id > '0' 
	AND NOW() < DATE_ADD( dateTime, INTERVAL 24 HOUR ) 
ORDER BY id 
DESC LIMIT 10
 
Zuletzt bearbeitet von einem Moderator:
Klingt logisch soweit. Gesagt, getan, glaube ich...

SQL: 
EXPLAIN SELECT *
FROM (
   SELECT *
   FROM `tabelle`
   WHERE NOW( ) < DATE_ADD( dateTime, INTERVAL 24 HOUR )
) AS t
WHERE (
   wert = '0'
   OR wert = '1000007202'
)
die Abfrage dauerte 3.6906 sek
Im Vergleich, vorher: die Abfrage dauerte 11.7870 sek.
Ist schonmal eine gute Ausbeute. Indizes sitzen weiterhin auf wert und dateTime.
Allerdings:

Resultat des Explain-Statements:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL |13028 | Using where; Using filesort
2 | DERIVED | tabelle | ALL | NULL | NULL | NULL | NULL | 2096143 | Using where

Nutzt er keinen Index als key? Wieso bei der DERIVED immernoch soviel rows und diesmal sogar über 2 MIO ...
 
Zuletzt bearbeitet von einem Moderator:
yaslaw hat gesagt.:
Was für eine Datenbank?
Zum Vergrößern anklicken....
Vom code her denk ich mal es ist MySQL

Asonsten würd ich auch mal das DATE_ADD umdrehen, so dass er es nicht für jede Zeile rechnen muss.
SQL: 
SELECT *
FROM (
   SELECT *
   FROM tabelle
   WHERE DATE_SUB(NOW( ), INTERVAL 24 HOUR) < dateTime
) AS t
WHERE (
wert = '0'
OR wert = '1000007202'
)

ggf. sogar das DATE_SUB in ein weiteres Subquery packen.
SQL: 
SELECT *
FROM (
   SELECT t.*
   FROM tabelle AS t
   		(SELECT DATE_SUB(NOW( ), INTERVAL 24 HOUR) AS dateTime) AS d
   WHERE d.dateTime < t.dateTime
) AS t
WHERE (
wert = '0'
OR wert = '1000007202'
)

Performance-Steigerungen gehen meist nur über pröbeln...

Er nimmt ev. keine Index weil er für das * eh einen FullTableScan machen muss.
Das kann man unter umständen auch verhindern, indem man zuerst nur die Daten Einschränkt und dabei nur die ID mitnimmt, diese am Schluss wieder zurückverknüpft.
SQL: 
SELECT
	tabelle.*
FROM
	tabelle,
	(	SELECT id
		FROM
			(	SELECT t.id
			   FROM tabelle AS t
			   		(SELECT DATE_SUB(NOW( ), INTERVAL 24 HOUR) AS dateTime) AS d
			   WHERE d.dateTime < t.dateTime
			) AS filtered
		WHERE wert = '0' OR wert = '1000007202'
	) AS filteredId
WHERE
	tabelle.id = filteredId.id

Das alles sind nur anregungen. Lass dich nicht ab komplizierten SQLs täuschen, sie sind bei vielen Datensätzen manchmal schneller

Nochmehr Nachtrag
Mach dich mal über die 2 SQL-Befehle schlau, falls du mit MySQL arbeitest
USE INDEX
FORCE INDEX
 
Zuletzt bearbeitet von einem Moderator:
Ja, es ist mySQL

Für's Foren-Archiv würde ich dich noch bitten, deinen DATE_SUB Sub-Query zu korrigieren ;)
Und hey, genau dort lag der Hund begraben ...
Hab die Anzahl rows nun dank dessen schonmal auf knapp 40.000 drücken können was sich auch stark bei der Zeit bemerktbar macht (0.11 secs)
und werde mir nun noch deine paar weiteren Hinweise ansehen.

Vielen Dank für die Mühe, du hast mir den Vormittag gerettet ;)

... Jepp, manchmal frage ich mich auch, was in den Köpfen meiner "Vor-Programmierer" vorging, hätte ohnehin an einige Stellen viele Dinge völlig anders gelöst / strukturiert / aufgebaut, aber das ist eine andere Geschichte ...

LG
 
Schön das ich helfen konnte.

Grundsätzlich gibt es einige Kleinigkeiten auf die man achten kann

- möglichst keine Funktionen auf alle Datensätze anwednen
- Möglichst früh auf möglichst wenig Daten reduzieren
- Indexe anhand des SQLs setzen, nicht umgekehrt
- Unterabfrage auf einen Index legen (Das alles was die Unterabfrage braucht, in einem Index drin ist. Also zum Beispiel ein Index über die Felder id und datetime). Dann gitbs ein indexscan und er braucht die Tabelle nicht.
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück