nicht angemeldet
Performance WHERE IN
Huhu, ich schonwieder ;)
ich erhoffe mir hier einen kleinen rat zu ergattern: ich habe eine ziemlich alte datenbank in mysql, die tabellen sind noch myisam
folgender aufbau:
CREATE TABLE `arbeitskarten` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`refid` bigint(20) NOT NULL DEFAULT '0',
`pos` int(11) NOT NULL DEFAULT '0',
`user` tinyblob NOT NULL,
`datum` date NOT NULL DEFAULT '0000-00-00',
`abnr` varchar(60) NOT NULL,
`kundename` tinytext NOT NULL,
`artnr` int(11) NOT NULL DEFAULT '0',
`text` longtext NOT NULL,
`lastchange_text` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`menge` decimal(5,1) NOT NULL DEFAULT '0.0',
`typ` varchar(30) NOT NULL DEFAULT '',
`untrtyp` varchar(30) NOT NULL DEFAULT '',
`ok` char(1) NOT NULL DEFAULT '0',
`beendet` char(1) NOT NULL DEFAULT '0',
`eingetragen_durch` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `refid` (`refid`),
KEY `datum` (`datum`),
KEY `user` (`user`(10)),
KEY `artnr` (`artnr`),
KEY `typ` (`typ`),
KEY `ok` (`ok`),
KEY `beendet` (`beendet`),
KEY `abnr` (`abnr`)
) ENGINE=MyISAM
dazu gibt es dann noch:
CREATE TABLE `arbeitskarten_zeiten` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`refid` bigint(20) NOT NULL DEFAULT '0',
`start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ende` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`artnr` bigint(20) NOT NULL DEFAULT '0',
`user` tinyblob NOT NULL,
PRIMARY KEY (`id`),
KEY `refid` (`refid`),
KEY `user` (`user`(10)),
KEY `ende` (`ende`),
KEY `artnr` (`artnr`)
) ENGINE=MyISAM
(ich weiß, artnr und user haben da nix mehr drin verloren, da die refid sich auf die id in der tabelle arbeitskarten bezieht, aber wie gesagt, is uralt ;)
nun ist es so, dass teilweise abfragen wie folgt getätigt werden:
SELECT SUM(TIME_TO_SEC(TIMEDIFF(ende, START))) AS zeit FROM arbeitskarten_zeiten WHERE ende!='0000-00-00 00:00:00' AND refid IN (SELECT id FROM arbeitskarten WHERE artnr='20' AND typ='reparatur')
solch eine abfrage dauert ziemlich lange, wenn ich diese abfragre ungecached ausführe auf meinem testsystem 6016 ms
(btf: diese abfrage liefert unter der tabelle arbeitskarten genau 1 einergebnis)
die tabellen ansich sind mittlerweile relativ groß
arbeitskarten hat 122522 zeilen
arbeitskarten_zeiten hat mittlerweile 104066 zeilen
(mich wundert, dass zweitere weniger zeilen hat ... naja, glaub da war mal ne umstellung wo einiges raus geflogen ist ...)
hat jemand eine idee wie man hier mit mysql noch schneller arbeiten könnte?
achja: mysql 5.5.16 Community Server
danke schonmal!
-
Tach!
ich erhoffe mir hier einen kleinen rat zu ergattern: ich habe eine ziemlich alte datenbank in mysql, die tabellen sind noch myisam
Ein kleiner Rat: Erstmal mit EXPLAIN anschauen, wie MySQL das Statement ausführt, dann sehen wir weiter. Und MyISAM ist nicht per se schlecht oder veraltet.
dedlfix.
-
Tach!
ich erhoffe mir hier einen kleinen rat zu ergattern: ich habe eine ziemlich alte datenbank in mysql, die tabellen sind noch myisam
Ein kleiner Rat: Erstmal mit EXPLAIN anschauen, wie MySQL das Statement ausführt, dann sehen wir weiter. Und MyISAM ist nicht per se schlecht oder veraltet.
dedlfix.
danke für den rat, das habe ich natürlich direkt gemacht:
die abfrage für die tabelle arbeitskarten ist ok, was mich jedoch wundert, ich habe ja in der tabelle arbeitskarten_zeiten extra einen index auf die refid gesetzt, da ich ja nach dieser oft suche, logisch
allerdings: der einzig von mysql als möglich angesehener schlüssel ist "ende"
benutzen tut mysql aber GARKEIN schlüssel
hier könnte ich mir schon als leihe vorstellen, dass das ja nicht so ist, wie es gewollt ist ;)hast du eine idee wieso das so sein könnte?
(hab auch direkt nochmal analyze table drüber laufen lassen, aber mysql meint, dass alles super ist ...)-
die abfrage für die tabelle arbeitskarten ist ok, was mich jedoch wundert, ich habe ja in der tabelle arbeitskarten_zeiten extra einen index auf die refid gesetzt, da ich ja nach dieser oft suche, logisch
allerdings: der einzig von mysql als möglich angesehener schlüssel ist "ende"
benutzen tut mysql aber GARKEIN schlüssel
hier könnte ich mir schon als leihe vorstellen, dass das ja nicht so ist, wie es gewollt ist ;)hast du eine idee wieso das so sein könnte?
(hab auch direkt nochmal analyze table drüber laufen lassen, aber mysql meint, dass alles super ist ...)also hier noch ein paar genauere analysen:
wenn ich die verkapselte select-anweisung einzeln ausführe dauert das 16 ms, das finde ich ok, dabei benutzt er dann auch den schlüssel artnr, so wie er es soll (wenn verkapselt dann nicht)
das ergebnis ist in diesem fall:
id
122521
122520so, also zwei ids. wenn ich jetzt die ursprüngliche abfrage nehme, und anstelle des verkapseltem selects einfach refid IN (122521,122520) nehme, dann nutzt er auch den refid-schlüssel und die ganze abfrage geht auch rucki zucki (unter 20 ms)
ich versteh das nich so wirklich, warum mysql da so unterschiedlich arbeitet
okok ich weiß, es ist der benutzer, der schuld ist ... nur weiß der benutzer grad nicht wie er es beheben kann ...-
Tach!
die abfrage für die tabelle arbeitskarten ist ok, was mich jedoch wundert, ich habe ja in der tabelle arbeitskarten_zeiten extra einen index auf die refid gesetzt, da ich ja nach dieser oft suche, logisch
Ein Index ist keine Pflichtveranstaltung für das DBMS. MySQL entscheidet von selbst und für jede Abfrage einzeln, ob ein Index nutzbringend verwendet werden kann oder nicht. Ein Index hilft insbesondere, wenn durch ihn die zu durchsuchende Datenmenge signifikant eingeschränkt werden kann. Sind zu wenige Datensätze in der Tabelle oder der gesuchte Wert ist in 99% der Datensätze zu finden, dann bringt ein Index nichts und wird nicht verwendet. Vielleicht helfen andere Indexe besser beim Einschränken, dann nimmt der Optimizer diese. (Meist nimmt er nur einen, aber wenn die Zwischenergebnismenge nach dem erstbesten Index immer noch sehr groß ist, nimmt er durchaus auch noch einen zweiten.)
allerdings: der einzig von mysql als möglich angesehener schlüssel ist "ende"
benutzen tut mysql aber GARKEIN schlüssel
hier könnte ich mir schon als leihe vorstellen, dass das ja nicht so ist, wie es gewollt ist ;)Ein Laie sollte sich Wissen zulegen und nicht nur leihen :-) Jedenfalls ist so ein Optimizer auch nur ein Mensch und kann sich irren. In dem Fall kann man ihm mit Index-Hints die Verwendung bestimmter Indexe nahelegen.
hast du eine idee wieso das so sein könnte?
Wenn ich so nachdenke, fällt mir ein Grund ein, warum das so sein könnte. Der muss aber nicht unbedingt mit der Realität übereinstimmen, auch wenn er erstmal plausibel klingen mag. Ich kann mir vorstellen, dass MySQL die Subquery als correlated subquery ansieht und sie für jeden Datensatz der äußeren Query neu berechnet. Die Überlegung hilft bei der Lösung aber nur, wenn ich dazu eine Gegenmaßnahme kennen würde. Erfolversprechender stelle ich mit jedoch vor, mal ein paar Umformulierungen des Statements zu probieren.
Joine die arbeitskarten A an die arbeitskarten_zeiten Z über z.refid=a.id und häng die WHERE-Bedingungen der Subquery an die äußere an.
Probier die A.WHERE-Bedingungen zur Join-Bedingung hinzuzufügen (keine Ahnung, ob das sinnvoll ist oder Pluspunkte bringt).
Joine nicht arbeitskarten sondern die Subquery.Immer schön messen, ob die Versuche was bringen. Wenn nicht müssen wir mal sehen, ob mir oder jemand anderem noch eine weitere Idee einfällt.
dedlfix.
-
-
-