[dbs] DB Speed Mysql

[Stefan Novak]

Hallo!

Ich habe ein Problem mit einem langsamen query:

SELECT k.id AS userid, k.kundennummer AS user_name, sum( d.bytes_in + d.bytes_out ) AS totalbytes, sum( d.bytes_in ) AS inbytes, sum( d.bytes_out ) AS outbytes, max( p.downloadvolumen ) AS maxvol FROM kunde k, produkte p, aironet_daten d WHERE k.trafficverrechnungsart = 'sperren' AND k.id = d.kunde AND k.p_id = p.p_id AND d.date >= '2007-04-01' AND d.date < '2007-05-01' GROUP BY k.id HAVING max( p.downloadvolumen ) < sum( d.bytes_in + d.bytes_out );

+--------+-----------+-------------+------------+-------------+--------------------+
| userid | user_name | totalbytes  | inbytes    | outbytes    | maxvol             |
+--------+-----------+-------------+------------+-------------+--------------------+
| 15xx   | xxxxx     | 5174676165  | 518204125  | 4656472040  | 4456028569.600000  |
| 37xx   | xxxxx     | 5010508039  | 2578990753 | 2431517286  | 4456028569.600000  |
| 42xx   | xxxxx     | 4600284777  | 2178771667 | 2421513110  | 4456028569.600000  |
| 43xx   | xxxxx     | 1447494958  | 641036587  | 806458371   | 1234803097.600000  |
+--------+-----------+-------------+------------+-------------+--------------------+
12 rows in set (2.66 sec) 

EXPLAIN SELECT k.id AS userid, k.kundennummer AS user_name, sum( d.bytes_in + d.bytes_out ) AS totalbytes, sum( d.bytes_in ) AS inbytes, sum( d.bytes_out ) AS outbytes, max( p.downloadvolumen ) AS maxvol FROM kunde k, produkte p, aironet_daten d WHERE k.trafficverrechnungsart = 'sperren' AND k.id = d.kunde AND k.p_id = p.p_id AND d.date >= '2007-04-01' AND d.date < '2007-05-01' GROUP BY k.id HAVING max( p.downloadvolumen ) < sum( d.bytes_in + d.bytes_out );
+----+-------------+-------+--------+------------------+------------+---------+----------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys    | key        | key_len | ref                  | rows  | Extra                                        |
+----+-------------+-------+--------+------------------+------------+---------+----------------------+-------+----------------------------------------------+
| 1  | SIMPLE      | d     | range  | kunde,date_kunde | date_kunde | 3       |                      | 90510 | Using where; Using temporary; Using filesort |
| 1  | SIMPLE      | k     | eq_ref | PRIMARY          | PRIMARY    | 4       | messwertedlv.d.kunde | 1     | Using where                                  |
| 1  | SIMPLE      | p     | eq_ref | PRIMARY          | PRIMARY    | 4       | messwertedlv.k.p_id  | 1     |                                              |
+----+-------------+-------+--------+------------------+------------+---------+----------------------+-------+----------------------------------------------+

Was ich nicht so ganz verstehe ist warum er filesort verwendet wenn er doch einen Key zum suchen hat.
Der table aironet_daten hat derzeit 85Mb Daten und 93 Mb Index. Die anderen Tables sind klein, sprich unter 5 Mb.

Thx Stefan

PS: Einige Daten habe ich verfälscht, was aber für das Problem egal sein sollte...