List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 18 1999 3:01pm
Subject:Re: struggle: JOIN + indexes
View as plain text  
Graham Ashton wrote:
> 
> I'm having a few problems getting to grips with optimising one of my
> queries, and have been grappling with the output of EXPLAIN. Still, I
> find all this stuff rather interesting, if a little confusing (I'll get
> there yet!).
> 
> I've been through chapter 10 of the manual (the chapter about optimising
> things) and think I'm starting to get the picture, but EXPLAIN seems to
> be telling me that my query is not as well optimised as it could be.
> 
> Here's a quick description of the situation. I have 2 tables, "bucket"
> and "bucket_set". I want to SUM() a range of values from the "bucket"
> table as follows;
> 
>         SELECT SUM(counter)
>           FROM bucket b, bucket_set bs
>          WHERE b.meas_id = 1
>            AND b.bucket_set_id = bs.id
>            AND bs.period = 'month'
>            AND bs.start_time = '1999-05-01 00:00:00';
> 
> The tables were created with;
> 
>     CREATE TABLE bucket (
>         meas_id INT NOT NULL,
>         id INT NOT NULL,
>         bucket_set_id INT NOT NULL,
>         counter INT NOT NULL,
>         UNIQUE (meas_id, bucket_set_id, id)
>     );
> 
>     CREATE TABLE bucket_set (
>         id INT NOT NULL AUTO_INCREMENT,
>         period ENUM ('hour', 'day', 'week', 'month', 'quarter',
>             'year') NOT NULL,
>         start_time DATETIME NOT NULL,
>         minimum INT NOT NULL,
>         maximum INT NOT NULL,
>         step INT NOT NULL,
>         type ENUM ('rtt', 'ott') NOT NULL,
>         PRIMARY KEY (id),
>         UNIQUE (period, start_time, minimum, maximum, step, type)
>     );
> 
> There are just short of 2 million rows in "bucket", 1001 of which match
> the WHERE definition. There are only 300 rows in "bucket_set".
> 
> Here's the output from EXPLAIN (I wrapped the text myself);
> 
> mysql> EXPLAIN   SELECT SUM(counter)
>     ->           FROM bucket b, bucket_set bs
>     ->          WHERE b.meas_id = 1
>     ->            AND b.bucket_set_id = bs.id
>     ->            AND bs.period = 'month'
>     ->            AND bs.start_time = '1999-05-01 00:00:00';
> +-------+-------+----------------+---------+---------+------------+
> | table | type  | possible_keys  | key     | key_len | ref        |
> +-------+-------+----------------+---------+---------+------------+
> | bs    | range | PRIMARY,period | NULL    |    NULL | NULL       |
> | b     | ref   | meas_id        | meas_id |       8 | ???,bs.id  |
> +-------+-------+----------------+---------+---------+------------+
> +-------+---------------------------+
> | rows  | Extra                     |
> +-------+---------------------------+
> |     1 | range used on key period  |
> | 12957 |                           |
> +-------+---------------------------+
> 2 rows in set (0.01 sec)
> 
> Now, from what I understand of the EXPLAIN docs, it's telling me that
> the indexes defined for the "bucket_set" table are being ignored. How
> can I fix things so that it goes faster? Can it be improved?
> 
> What I don't understand from the manual is how MySQL processes queries
> that rely on indexes spread across multiple tables. I notice that it
> mentions that;
> 
>   "Any index that doesn't span all AND levels in the WHERE clause is not
>    used to optimize the query."
> 
> I think that my indexes do span all the AND levels, but would appreciate
> some comments from a more experienced MySQL-er.
> 
> Thanks - any ideas would be much appreciated. I'm using version 3.21.33
> on Debian Linux.
> 
> P.S. I came to MySQL as a database newbie - I've not only using/learning
>      about it very useful and educational, but fun too.
> 
> --
> Graham

Hi Graham

Mysql uses only one key per table to solve a query.
Because of that you don't have a key spanning the whole "bucket_set" part in your query.
Create another KEY which spans id, period and start_time.
This will speed up your query.

Tschau
Christian

Thread
struggle: JOIN + indexesGraham Ashton18 May
  • Re: struggle: JOIN + indexesChristian Mack18 May