Hi, I have a problem with SELECT speed. The first execution takes up to several minutes while the next (with the same statement) takes not more then several seconds.
The statement example is: select nas.nasIpAddress, count(distinct(acct.user_id)), count(*), sum(acct.acctOutputOctets) from acct, nas, can, acctStatusType where acct.can_id = can.id and acct.acctStatusType_id = acctStatusType.id and acct.day >= '2007-09-01' and acct.day <= '2007-11-01' and acct.nas_id = nas.id and can.can = 10 and acctStatusType.acctStatusType = 'Stop' group by acct.nas_id ; EXPLAIN shows the following: +----+-------------+----------------+--------+------------------------+- ---------------+---------+-----------------+-------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+--------+------------------------+- ---------------+---------+-----------------+-------+----------------+ | 1 | SIMPLE | can | const | PRIMARY,can | can | 2 | const | 1 | Using filesort | | 1 | SIMPLE | acctStatusType | const | PRIMARY,acctStatusType | acctStatusType | 10 | const | 1 | | | 1 | SIMPLE | acct | ref | index1 | index1 | 4 | const,const | 63827 | Using where | | 1 | SIMPLE | nas | eq_ref | PRIMARY | PRIMARY | 4 | GWF.acct.nas_id | 1 | | +----+-------------+----------------+--------+------------------------+- ---------------+---------+-----------------+-------+----------------+ I have the following table with 59742411 rows: mysql> describe acct; +-----------------------+-----------------------+------+-----+---------- -----------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+-----------------------+------+-----+---------- -----------+-------+ | date | datetime | | | 0000-00-00 00:00:00 | | | user_id | int(10) unsigned | | PRI | 0 | | | nas_id | int(10) unsigned | | PRI | 0 | | | can | smallint(5) unsigned | YES | | NULL | | | can_id | smallint(5) unsigned | YES | MUL | NULL | | | acctStatusType_id | tinyint(3) unsigned | | PRI | 0 | | | acctTerminateCause_id | tinyint(3) unsigned | | | 0 | | | sweetEventContext_id | tinyint(3) unsigned | | | 0 | | | acctSessionId | varchar(8) | | PRI | | | | acctDelayTime | mediumint(8) unsigned | | | 0 | | | acctSessionTime | mediumint(8) unsigned | YES | | NULL | | | acctInputOctets | bigint(20) unsigned | YES | | NULL | | | acctOutputOctets | bigint(20) unsigned | YES | | NULL | | | wisprBwMaxUp | int(10) unsigned | YES | | NULL | | | wisprBwMaxDown | int(10) unsigned | YES | | NULL | | | day | date | | PRI | 0000-00-00 | | | acctMultiSessionId | varchar(27) | YES | | NULL | | +-----------------------+-----------------------+------+-----+---------- -----------+-------+ mysql> show index from acct; +-------+------------+----------+--------------+-------------------+---- -------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------------+---- -------+-------------+----------+--------+------+------------+---------+ | acct | 0 | PRIMARY | 1 | user_id | A | 53341 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY | 2 | nas_id | A | 277871 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY | 3 | acctStatusType_id | A | 558340 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY | 4 | acctSessionId | A | 59742411 | NULL | NULL | | BTREE | | | acct | 0 | PRIMARY | 5 | day | A | 59742411 | NULL | NULL | | BTREE | | | acct | 1 | index1 | 1 | can_id | A | 467 | NULL | NULL | YES | BTREE | | | acct | 1 | index1 | 2 | acctStatusType_id | A | 936 | NULL | NULL | | BTREE | | | acct | 1 | index1 | 3 | day | A | 88638 | NULL | NULL | | BTREE | | | acct | 1 | index1 | 4 | nas_id | A | 1659511 | NULL | NULL | | BTREE | | +-------+------------+----------+--------------+-------------------+---- -------+-------------+----------+--------+------+------------+---------+ MySQL version is 4.1.20 What is the bottleneck in my server? How could I improve MySQL server performance? Thank you! -- Alexander Bespalov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
