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
