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" <bespalov@stripped>
To: <mysql@stripped>
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/mysql?unsub=1
>
>
