MySQL Forums
Forum List  »  Performance

Performance Problems
Posted by: ICI MASA
Date: October 04, 2005 09:50AM

I have this simple query which takes to long. The Database has about 30.000.000 rows the query takes about 30 sec to run.

Query:
SELECT NID, Vaddr, AVG(Vvalue) AS avgValue
FROM tbValue
WHERE DID = #getAccList.DID# AND NID = #getAccList.NID# AND VtimeStamp BETWEEN #varStartTime# AND #varEndTime#
GROUP BY NID

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbValue ref Index_2,Index_3,Index_5 Index_3 5 const 8378 Using where

Describe:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbValue ref Index_2,Index_3,Index_5 Index_3 5 const 19322 Using where

Show index:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
tbValue 0 PRIMARY 1 VID A 27546276 (null) BTREE (null)
tbValue 1 Index_2 1 VtimeStamp A 765174 (null) BTREE (null)
tbValue 1 Index_3 1 DID A 17 YES BTREE (null)
tbValue 1 Index_5 1 NID A 17 YES BTREE (null)

Regards
Martin

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance Problems
1805
October 04, 2005 09:50AM
1200
October 04, 2005 06:49PM
1189
October 05, 2005 03:40AM


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.