[dbs] DB Speed Mysql

[Sebastian Mendel]

Stefan Novak schrieb:
> 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 );

ich habe es mal etwas lesbarer umgeschrieben:

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
LEFT JOIN
	produkte p
ON	k.p_id = p.p_id
LEFT JOIN
	aironet_daten d
ON	k.id = d.kunde
AND	d.date >= '2007-04-01'
AND	d.date < '2007-05-01'
WHERE
	k.trafficverrechnungsart = 'sperren'
GROUP BY
	k.id
HAVING
	maxvol < totalbytes;


> [...]
> 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.

wie genau sieht der index aironet_daten.date_kunde aus?
hast du es mal mit einem index nur auf aironet_daten.date probiert?


-- 
Sebastian Mendel

www.sebastianmendel.de