MySQL Forums
Forum List  »  German

Re: Performance bei SELECT
Posted by: Mark Knochen
Date: January 20, 2012 05:33AM

Hallo Thomas,

die Spalte PhysicalStock ist jetzt INT ...
key_buffer_size sind auf 20% gesetzt -> das sind in dem Fall 1,6 GB

Query DISTINCT ist auf die benötigten Spalten reduziert.

>> SELECT MAX(LENGTH(brand)), MAX(LENGTH(attr_gender)) FROM items;

+--------------------+--------------------------+
| MAX(LENGTH(brand)) | MAX(LENGTH(attr_gender)) |
+--------------------+--------------------------+
|                 57 |                        1 |
+--------------------+--------------------------+


Und nun der neue Explain vom Select

EXPLAIN SELECT items.Brand, 
       SUM(
            (SELECT SUM(its.PhysicalStock)
              FROM items_stock AS its
             WHERE its.ItemNrInt = items.ItemNrInt
           )
       ) AS stockValue
FROM items
LEFT JOIN ( SELECT DISTINCT ItemNrInt,OrderQty,FullPrice,OrderDate,ItemPriceNoVAT,Status FROM invoicelines) invoicelines
ON (invoicelines.ItemNrInt = items.ItemNrInt)
WHERE items.attr_gender = '8'
GROUP BY items.Brand;




+----+--------------------+--------------+------+---------------+-----------+---------+---------------------------------+--------+----------------------------------------------+
| id | select_type        | table        | type | possible_keys | key       | key_len | ref                             | rows   | Extra                                        |
+----+--------------------+--------------+------+---------------+-----------+---------+---------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY            | items        | ALL  | NULL          | NULL      | NULL    | NULL                            |  69093 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | <derived3>   | ALL  | NULL          | NULL      | NULL    | NULL                            | 498768 |                                              |
|  3 | DERIVED            | invoicelines | ALL  | NULL          | NULL      | NULL    | NULL                            | 499560 | Using temporary                              |
|  2 | DEPENDENT SUBQUERY | its          | ref  | ItemNrInt     | ItemNrInt | 4       | pixi_imperial_2.items.ItemNrInt |      8 |                                              |
+----+--------------------+--------------+------+---------------+-----------+---------+---------------------------------+--------+----------------------------------------------+

Die Abfrage so dauert ca. 260 sec :)


Es ist wirklich der JOIN auf die Invoicelines ... ohne den ist die Abfrage schnell.

Möglicherweise muss ich den in ein SubSelect auslagern - das würde mir aber nicht so sehr gefallen


Danke schonmal

Options: ReplyQuote


Subject
Views
Written By
Posted
2744
January 19, 2012 07:17AM
1523
January 19, 2012 12:59PM
Re: Performance bei SELECT
1585
January 20, 2012 05:33AM
1810
January 20, 2012 06:10AM
1533
January 20, 2012 07:06AM
1572
January 20, 2012 12:11PM
1535
January 25, 2012 04:11AM
1381
January 25, 2012 04:24AM
1426
January 25, 2012 04:30AM
1365
January 25, 2012 04:49AM
1203
January 25, 2012 06:05AM
1493
January 25, 2012 06:37AM
1467
January 25, 2012 07:46AM
1555
January 26, 2012 12:57AM
1468
January 25, 2012 11:11PM
1307
January 25, 2012 11:50PM
1216
January 27, 2012 08:27AM
1616
January 27, 2012 06:21AM
1465
February 02, 2012 10:06PM
1162
February 03, 2012 05:34AM
1419
February 03, 2012 02:04PM
1358
February 08, 2012 09:52PM
1386
February 09, 2012 08:41AM
1306
February 10, 2012 02:03AM
1545
February 10, 2012 02:52AM
1443
February 10, 2012 04:51AM
1412
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.