Abfrage mit MySQL ohne Ergebnis

[flyer81]

Hallo,
habe folgende Tabelle und möchte gerne alle Datensätze gruppiert nach Location und Produkten haben, welche zuletzt bearbeitet worden sind in einem bestimmten Jahr.

Wenn ich diese Abfrage nun mit MySQL 5.5.46 (Installiert auf Server) oder 5.5.32 (Laptop mit Xampp 1.8.2) durchführe, so kommt es zu keinem Ergebnis (es wird nur geladen und geladen....).
Führe ich die Abfrage nun mit meinem Rechner aus, auf welchem Xampp 7.0.3 mit 10.1.10-MariaDB installiert ist, so ist das Ergebnis nach 0.03sek da.

Mit der Abfrage müsste MySQL eigentlich gut zurecht kommen. Weiß jemand rat?


Tabelle:
CREATE TABLE `material_account` (
`ma_id` int(11) NOT NULL,
`ma_produkt_id` int(11) NOT NULL,
`ma_location_id` int(11) NOT NULL,
`ma_stock` int(11) NOT NULL,
`ma_created_ur_id` int(11) NOT NULL,
`ma_created_timestamp` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Abfrage:
SELECT

ma_id,
ma_location_id,
ma_product_id,
ma_stock,
ma_created_ur_id,
ma_created_timestamp​

FROM

material_account​

WHERE

(ma_location_id, ma_product_id, ma_created_timestamp) IN (

SELECT

ma_mat_id,
ma_mp_id,
MAX(ma_created_timestamp)​

FROM

material_account​

WHERE

date_format(ma_created_timestamp, '%Y') = '2015'​

GROUP BY

ma_location_id,
ma_product_id)​

ORDER BY

ma_location_id ASC,
ma_product_id ASC​

 

[ukulele]

Schonmal mit InnoDB versucht? Über wieviele Datensätze in der Tabelle und in der Ausgabe reden wird?

 

[flyer81]

Habe die Tabelle auf InnoDB geändert, allerdings keine Besserung.
Datensätze in der Tabelle ca. 20.000.
In der Ausgabe ca. 100

 

[flyer81]

Es hat sich im Beitrag ein Fehler in der aufgeführten Unterabfrage eingeschlichen,
diese ist natürlich richtigerweise und nicht wie oben dargestellt....
...
(SELECT

ma_location_id,
ma_product_id,
MAX(ma_created_timestamp)​

FROM ...)


und noch ergänzend die Fehlermeldung:

( ! ) Fatal error: Maximum execution time of 300 seconds exceeded in C:\Programme\xampp\phpMyAdmin\libraries\dbi\mysqli.dbi.lib.php on line 267
Call Stack
# Time Memory Function Location
1 0.0000 224792 {main}( ) ..\import.php:0
2 1.4219 6117336 include( 'C:\Programme\xampp\phpMyAdmin\sql.php' ) ..\import.php:614
3 227.0938 6901856 PMA_DBI_try_query( ) ..\sql.php:821
4 227.0938 6901920 PMA_DBI_real_query( ) ..\database_interface.lib.php:192

 

[ukulele]

MySQL akzeptiert ja die Syntax, die Meldung ist nur ein Timeout. Warum die Performance so hängt kann ich nicht erkennen. EXPLAIN kann MySQL nicht oder?

 

[akretschmer]

das Explain von MySQL willst Du eher nicht sehen ...

 

[Walter]

Entweder hast Du keine Indexe auf die passenden Spalten gelegt oder Mysql ist falsch konfiguriert (z.B. zu wenig Speicher zugewiesen etc etc)

 

[akretschmer]

für die innere Abfrage kann MySQL keinen Index nutzen, dazu wäre ein funktionaler Index nötig, das kann MySQL nicht. Möglicherweise wird dort schon die meiste Zeit verbraten. Und so eine where (...) in (...) ist in MySQL auch ned so der Bringer, ich glaube, das geht immer nur über temp. Tabellen, die MySQL auf Platte anlegt. Das ist aber, glaube ich, in aktuellen Versionen besser geworden. Aber hier kommt erschwerend dazu, daß das IN(...) innen wieder ein Select ist und damit schwerer planbar. Evtl. wäre ein Umschreiben auf einen Join hier besser.

@ukulele es ist egal, ob das MyISAM oder InnoDB ist, das spielt da keine Rolle.

 

[flyer81]

Hallo Walter,
ein Index wurde über die Spalten (ma_location_id,
ma_product_id, ma_created_timestamp) gelegt, das hat leider auch keine Verbesserung gebracht.

Hat jemand einen Vorschlag für die Umschreibung auf einen Join?

 

[ukulele]

WHERE (...) IN (...) geht bei MSSQL z.B. gar nicht als Syntax, wundert mich das MySQL das akzeptiert. Daher halte ich mich auch zurück, ich weiß nicht wie MySQL das am performantesten hinbekommt.

 

[akretschmer]

Du hast:

test=*# with x as (select s as a, s*10 b, s*100 c from generate_series(1,10) s) select * from x inner join (select y as y,y*10 z from generate_series(3,6) y) bla on (x.a, x.b) = (bla.y, bla.z);
 a | b  |  c  | y | z
---+----+-----+---+----
 3 | 30 | 300 | 3 | 30
 4 | 40 | 400 | 4 | 40
 5 | 50 | 500 | 5 | 50
 6 | 60 | 600 | 6 | 60
(4 Zeilen)

und suchst

test=*# with x as (select s as a, s*10 b, s*100 c from generate_series(1,10) s) select * from x where (a,b) in (select y,y*10 from generate_series(3,6) y);
 a | b  |  c
---+----+-----
 3 | 30 | 300
 4 | 40 | 400
 5 | 50 | 500
 6 | 60 | 600
(4 Zeilen)

Die Anpassung an Deine Bedürfnisse überlasse ich Dir zur Übung.

 

[flyer81]

Vielen Dank Euch allen,
mit der Hilfestellung von akretscher hat es geklappt.

Hier der Code für Interessierte...

SELECT
*
FROM material_account
INNER JOIN (

SELECT ma_product_id,ma_location_id, MAX(ma_created_timestamp) AS max_timestamp
FROM material_account
WHERE date_format(ma_created_timestamp, "%Y") = '2015'
GROUP BY ma_location_id, ma_product_id) table2
ON(material_accountma_product_id, material_account. ma_product_id, material_account.ma_created_timestamp) =
(table2.ma_location_id, table2.ma_product_id, table2.max_timestamp)​

ORDER BY
ma_location_id ASC,
ma_product_id ASC

 

[akretschmer]

you are welcome ;-)