[MySQL] Abfrage Optimierung

[NagathoR]

Hallo,

folgendes Szenario:

Tabelle Sales (s) mit jede Menge Abverkaufsdaten.
ArtikelNr,Kassenbon,HändlerID,Datum

Jetzt sollen alle zu einem gekauften Artikel ebenfalls augelistet werden, d.h. was wurde mit dem Artikel zusätzliche gekauft

Bisher habe ich das mit PHP gemacht, nur macht jetzt bei 5 Mio Datensätze in den Abverkäufen PHP schlapp. Darum meine Frage kann ich effizienter mit MySQL machen?

Bisher

SELECT ArtikelNr,Kassenbon,Haendler,Datum
FROM sales s
WHERE ArtikelNr = '10099'

SCHLEIFE mit Subselect

SELECT * FROM sales
WHERE ArtikelNr='$ArtikelNr' AND Kassenbon='$Kassenbon' AND Haendler='$Haendler' AND
Datum='$Datum'

Danach die Ergebnisse aufsummiert.


Geht das effizienter?

 

[ZeroCCC]

Du willst wissen was zu einem Artikel noch dazu gekauft wurde?

Dazu Joins du einfach die Tabelle mit sich selbst.

SELECT
	*
FROM
	sales INNER JOIN
	sales products ON (sales.Kassenbon = products.Kassenbon)
WHERE
	sales.ArtikelNr = 'gesuchts produkt' AND
	products.ArtikelNr != 'gesuchts produkt'

Ich hab jetzt einfach mal Kassenbon als eindeutig angenommen...

 

[NagathoR]

Ich kann das ganze mal testen, aber ich gehe eigentlich davon aus, ne Tabelle mit 5mio einträgen mit sich selbst Mergen, dat kann nicht gut sein von der Performance, aber ich lass mich gern Überzeugen.

Ich teste es gleich mal durch.

 

[NagathoR]

Also bei dem ganzen scheint mir übel zu werden.
54042 * 28151 = 1521336342 Reihen die miteinander verbunden werden müssen.

Bis jetzt hab ich 5 Minuten gewartet aber da ist echt nichts passiert.
Da ging sogar die vorherige Abfrage schneller

Hat jemand noch andere Ideen?

EXPLAIN 
SELECT s2.Artnr
FROM `sales` s
LEFT JOIN `sales` s2 ON (
 s2.bon = ps.bon AND 
 DATE(s2.bondate) = DATE(s.bondate) 
 AND s.haendler=s2.haendler
)
WHERE 
s.ArtNr=1917 AND 
s.bondate >= '2007-10-01'  AND s.bondate <= '2007-12-01'

 

[NagathoR]

So kam nun schon ein ganzes Stück weiter.

Habe mal die Indexe auf die benötigten Felder gesetzt bzw neu gesetzt.
Haendler, Bon, Bondate, ArtNr

SELECT s2.ArtNr, s2.vkp AS VKP, SUM( s2.Menge ) AS Menge
FROM `sales` s
INNER JOIN `sales` ps2 ON ( s2.bon = ps.bon AND DATE( s2.bondate ) = DATE( s.bondate )
AND ps.haendler = s2.haendler )
WHERE s.ArtNr =1917
AND s.bondate >= '2007-08-01'
AND s.bondate <= '2007-08-31'
AND s.cardnr IS NULL
GROUP BY s2.ArtNr

Dabei will ich noch rauskriegen, was wie oft verkauft wurde und mit welchem generierten Umsatz. Danach kann man ja schön dem User ähnliche Produkte anbieten.

Problem ist aber, dass jetzt wieder die Abfrage schlechter wird.


id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s2 ALL Haendler NULL NULL NULL 5296164 Using temporary; Using filesort
1 SIMPLE s ref Haendler,cardnr,ArtNr,bondate,ArtNr2 12 const,s2.Haendler,db163208.s2.bon 1 Using where; Using index

 

[ZeroCCC]

Ich würde vielleicht mal die Indezien richtig setzen und nutzen bzw mal die Tabellenstruktur überdenken. Schon alleine die tatsache das die Datenbank mit s2 den Query beginnt und ein full tablescan macht ist ja absolut falsch.

Poste mal die Struktur der Tabelle mit Keys... aber schon das: "DATE( s2.bondate ) = DATE( s.bondate )" ist ein absolutes no go, das wird wahrscheinlich auch der Auslöser für den tablescan sein.

PS: bei mir läuft sowas auch, zwar nicht mit 5Mio Datensätzen, aber ~550.000 Datensätzen und das ganze dauert ungefähr 0.02-0.05 Sekunden. Bei mir sieht das Explain aber auch so aus wie mans erwartet.

 

[NagathoR]

CREATE TABLE IF NOT EXISTS `sales` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `Haendler` int(10) NOT NULL default '0',
  `ArtNr` mediumint(5) unsigned NOT NULL default '0',
  `Bon` mediumint(7) unsigned NOT NULL default '0',
  `Bondate` datetime NOT NULL default '0000-00-00 00:00:00',
  `VKP` float(5,2) NOT NULL default '0.00',
  `Menge` mediumint(6) NOT NULL default '0',
  `Cardnr` bigint(13) default NULL,
  PRIMARY KEY  (`ID`),
  KEY `Haendler` (`Haendler`,`ArtNr`,`Bon`,`Bondate`),
  KEY `Haendler_2` (`Haendler`),
  KEY `ArtNr` (`ArtNr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Test Tabelle' AUTO_INCREMENT=5296165 ;

Das Bondate liegt im DateTime Format vor.
Ich brauch aber das Datum mit, da der Bon nicht Unique ist, KEY (Bon,Haendler, DATE(Bondate), Artnr)

 

[NagathoR]

Also bei mir ist es Wurst, was ich wie rum verwende.

SELECT s.ArtNr, s.vkp AS VKP, SUM( s.Menge ) AS Menge
FROM `sales` s
INNER JOIN `sales` s2 ON ( s2.bon = s.bon AND  s.haendler = s2.haendler )
WHERE s2.ArtNr =1917
AND s2.bondate >= '2007-08-01'
AND s2.bondate <= '2007-08-31'
AND s2.cardnr IS NULL
GROUP BY s.ArtNr

5Mio Datensätze und bei dem Statement brauch der SQL 25s. Ist fürs Web schon zu lang.

 

[ZeroCCC]

Hat Bondate unterschleidliche Uhrzeiten für jede Position auf einen Bon? Wenn ja ist der Key "Handler" sinnlos. Der kann so nicht genutzt werden um die Positionen eines Bons zu ermitteln. Wenn nein wäre die Tabellenstruktur "falsch", das wäre dann eine eindeutig 1:N Beziehung. (mit Handler und Bon würde ich noch mitgehen in einer Tabelle, aber wenn das Datum für einen Bon gleich ist nicht mehr)

Also die Lösung ist eigentlich ganz Simple... du brauchst einen Index der die Position eines Bons festhällt. Wenn Bondate gleich ist dann hast du den bereits, musst ihn bloss richtig nutzen. Also "DATE( s2.bondate ) = DATE( s.bondate )" geht gar nicht...

Ansonsten musst du dir ein Kopf machen wie du da ein Index für hinbekommst. Dazu brauchst du meiner Meinung nach auf jedenfall eine neue Spalte und in der gibst du jeden Bon eine Eindeutige Nummer. (Kann zb auch ein Hash in Form von "MD5(CONCAT(bon,Haendler,DATE(Bondate)))" sein.)

 

[ZeroCCC]

Also bei mir ist es Wurst, was ich wie rum verwende.

Da hast du was falsch verstanden... es ist egal wie du die JOINs schreibst, solange du Mysql nicht zwingst die Reihnfolge zu behalten "optimiert" Mysql selbst die Reihnfolge wie die Tabellen gejoint werden. Was Mysql da macht siehst du mit Explain... Und solang bei Type irgendwo ALL steht ist das bei 5Mio Datensätzen "tötlich", das heißt Mysql nutzt kein Index sondern scannt die ganze Tabelle.

Zb hier:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s2 ALL Haendler NULL NULL NULL 5296164 Using temporary; Using filesort
1 SIMPLE s ref Haendler,cardnr,ArtNr,bondate,ArtNr2 12

Was du willst ist eigentlich andere Positionen auf Bons zufinden die einen Bestimmten Artikel beinhalten. Logischer weiße sucht man sich also als erstes alle Bons die den bestimmten Artikel beinhalten (Tabelle s). Dann hohlt man sich die Artikel von den Bons (s2) und Gruppiert diese.
Aber was macht hier Mysql? Mysql beginnt aber in dem fall mit der Suche von der falschen Seite (S2). Es geht sämtliche Datensätze in S2 durch Joint diese mit S1 und prüft ob S1 den gesuchten Artikel beinhaltet. Ist ein bissel umständlich wie ich finde

*edit* achja... man könnte Mysql zwar dazu zwingen richtigherum an die Sache ranzugehen, aber ich bin der Meinung das ist nicht wirklich zweckdienlich. Die optimierung von Mysql ist eigentlich sehr gut und wenns da Probleme gibt weißt das auf ein fehlerhaftes Designs hin. (Ich hatte bis jetzt nur 2 mal den fall das Mysql wirklich scheiße gebaut hat und man manuel eingreifen musste, und ein klein wenig erfahrung hab ich auch schon Das trit also äusserst selten auf...)

 

[NagathoR]

Ich teste das ganze mit dem Hash mal durch, auf die Idee hätte ich aber auch kommen können. *hmm*

Melde mich dann gleich wieder.


UPDATE:
Mit dem Extra-Hash geht es wunderbar, die Schnelligkeit ist nun nicht zu überbieten.
Danke für den Ideen-Anstoss