List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:November 26 2007 4:14pm
Subject:Re: SELECT Speed
View as plain text  
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
>
>

Thread
SELECT SpeedAlexander Bespalov26 Nov
  • Re: SELECT SpeedMartin Gainty26 Nov
  • Re: SELECT SpeedAlex Arul Lurthu27 Nov
    • Re: SELECT Speedmos27 Nov
      • Re: SELECT SpeedPerrin Harkins27 Nov