From b877d63356e0e0df711e84223bd6832114a91f70 Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Mon, 25 Feb 2019 16:03:29 +0100 Subject: [PATCH] =?utf8?q?Neuer=20index=20auf=20inventory=20=C3=BCber=20it?= =?utf8?q?ime=20und=20parts=5Fid?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Um Abfragen wie * letzte 10 Lagerbuchungen * letzte 10 Lagerbuchungen von Artikel XYZ zu beschleunigen --- SL/DB/Part.pm | 2 +- sql/Pg-upgrade2/inventory_itime_parts_id_index.sql | 13 +++++++++++++ 2 files changed, 14 insertions(+), 1 deletion(-) create mode 100644 sql/Pg-upgrade2/inventory_itime_parts_id_index.sql diff --git a/SL/DB/Part.pm b/SL/DB/Part.pm index 9eb76df6a..db3a26663 100644 --- a/SL/DB/Part.pm +++ b/SL/DB/Part.pm @@ -373,7 +373,7 @@ sub get_simple_stock_sql { LEFT JOIN warehouse w ON (i.warehouse_id = w.id) LEFT JOIN bin b ON (i.bin_id = b.id) WHERE parts_id = ? - GROUP BY w.description, b.description, p.unit, i.parts_id + GROUP BY w.description, w.sortkey, b.description, p.unit, i.parts_id HAVING SUM(qty) != 0 WINDOW pt AS (PARTITION BY i.parts_id ORDER BY w.sortkey, b.description, p.unit), wh AS (PARTITION by w.description ORDER BY w.sortkey, b.description, p.unit) diff --git a/sql/Pg-upgrade2/inventory_itime_parts_id_index.sql b/sql/Pg-upgrade2/inventory_itime_parts_id_index.sql new file mode 100644 index 000000000..36cd99c9d --- /dev/null +++ b/sql/Pg-upgrade2/inventory_itime_parts_id_index.sql @@ -0,0 +1,13 @@ +-- @tag: inventory_itime_parts_id_index +-- @description: Index auf inventory itime und parts_id, um schnell die letzten Transaktion raussuchen zu können +-- @depends: release_3_5_4 + +-- increase speed of queries such as + +-- last 10 entries in inventory: +-- SELECT * FROM inventory ORDER BY itime desc LIMIT 10 + +-- last 10 inventory entries for a certain part: +-- SELECT * FROM inventory WHERE parts_id = 1234 ORDER BY itime desc LIMIT 10 + +CREATE INDEX inventory_itime_parts_id_idx ON inventory (itime, parts_id); -- 2.20.1