MySQL Forums
Forum List  »  German

Re: Performance bei SELECT
Posted by: Thomas Wiedmann
Date: January 27, 2012 06:21AM

Hallo Mark,

ja sieht schwierig aus. Hast Du mal in letzter Zeit die beteiligten Tabellen mit ANALYZE und OPTIMIZE optimiert? Wenn nein, mal testen.

z. B.
mysql> ANALYZE TABLE items;
+------------+---------+----------+-----------------------------+
| Table      | Op      | Msg_type | Msg_text                    |
+------------+---------+----------+-----------------------------+
| test.items | analyze | status   | Table is already up to date |
+------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> OPTIMIZE TABLE items;
+------------+----------+----------+-----------------------------+
| Table      | Op       | Msg_type | Msg_text                    |
+------------+----------+----------+-----------------------------+
| test.items | optimize | status   | Table is already up to date |
+------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql>

Nun noch mal eine Kardinalitätsabfrage
SELECT COUNT(DISTINCT attr_department), 
       COUNT(DISTINCT ItemNrInt),
       COUNT(*)
  FROM items;

und noch ein Test mit einem weiteren combined Index
ALTER TABLE items
 DROP KEY attr_department,
 ADD KEY idx_department_item_nr_brand (attr_department, `ItemNrInt`, `Brand` );

Jetzt bitte nochmal den kompletten Query mit einem Filter auf attr_department einen EXPLAIN ausführen und Query plus EXPLAIN hier zeigen.

Wenn dass nichts hilft mal folgende Radikallösung. Ein gigantischen Index auf invoicelines mit den fünf beteiligten Spalten aus dem "LEFT JOIN auf invoicelines". Ich versuche hiermit einen "Key-Only-Access", der Nachteil ist, dass ein INSERT, UPDATE, DELETE langsamer wird.
ALTER TABLE invoicelines
 DROP KEY ItemNrInt,
 ADD KEY idx_query_speep_up (ItemNrInt, OrderQty, FullPrice, OrderDate, ItemPriceNoVAT );

Jetzt bitte nochmal den kompletten Query mit einem Filter auf attr_department einen EXPLAIN ausführen und Query plus EXPLAIN hier zeigen.

Bis dahin..

Grüße
Thomas

Options: ReplyQuote


Subject
Views
Written By
Posted
2755
January 19, 2012 07:17AM
1528
January 19, 2012 12:59PM
1590
January 20, 2012 05:33AM
1814
January 20, 2012 06:10AM
1538
January 20, 2012 07:06AM
1576
January 20, 2012 12:11PM
1540
January 25, 2012 04:11AM
1386
January 25, 2012 04:24AM
1432
January 25, 2012 04:30AM
1369
January 25, 2012 04:49AM
1208
January 25, 2012 06:05AM
1499
January 25, 2012 06:37AM
1471
January 25, 2012 07:46AM
1559
January 26, 2012 12:57AM
1473
January 25, 2012 11:11PM
1312
January 25, 2012 11:50PM
1220
January 27, 2012 08:27AM
Re: Performance bei SELECT
1621
January 27, 2012 06:21AM
1469
February 02, 2012 10:06PM
1168
February 03, 2012 05:34AM
1424
February 03, 2012 02:04PM
1365
February 08, 2012 09:52PM
1391
February 09, 2012 08:41AM
1311
February 10, 2012 02:03AM
1550
February 10, 2012 02:52AM
1448
February 10, 2012 04:51AM
1416
February 09, 2012 11:58PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.