You seem to have an over-reliance on BTREE Indexes over BITMAPPED Indexes or HASH Indexes
There are specific rules governing implementation of BTREE Index http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.h tm#sthref893 As well as specific rules governing use of HASH Index http://www.geekinterview.com/question_details/28844 not to forget rules governing use of BITMAP indexes http://www.dba-oracle.com/oracle_tips_bitmapped_indexes.htm M- ----- Original Message ----- From: "Alexander Bespalov" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Monday, November 26, 2007 10:03 AM Subject: SELECT Speed > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
