Thanks for the suggestion, but no luck. Here's the explain output for, in order, my original query, the 2-part query, and the explicit join (note, not quite like my first post, I had "cleaned up" my tables to simplify the situation):
mysql> explain SELECT * FROM accounts, transactions WHERE canon_name =
'wms' AND state = 'active' AND id = account_id AND date_stamp BETWEEN
1096588800001 AND 1099267200001;
+--------------+------+-----------------------+------------+---------+-------------+------+-------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+--------------+------+-----------------------+------------+---------+-------------+------+-------------+
| accounts | ref | PRIMARY,canon_name | canon_name | 11 | const,const |
1 | Using where |
| transactions | ref | date_stamp,account_id | account_id | 3 | accounts.id |
35 | Using where |
+--------------+------+-----------------------+------------+---------+-------------+------+-------------+
2 rows in set (0.01 sec)
The "account_id" key is a 2 column key, "date_stamp" is an 8 byte int
(java-style date stamp), so you can see only the 3-byte account_id part
of the key is used. If I look up the "wms" account ID and plug that in
directly, basically doing two seperate selects to get the data I want, I
get:
mysql> explain SELECT * FROM transactions WHERE account_id = 45 AND
date_stamp BETWEEN 1096588800001 AND 1099267200001;
+--------------+-------+-----------------------+------------+---------+------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+--------------+-------+-----------------------+------------+---------+------+------+-------------+
| transactions | range | date_stamp,account_id | account_id | 11 | NULL | 1 |
Using where |
+--------------+-------+-----------------------+------------+---------+------+------+-------------+
1 row in set (0.02 sec)
Now you can see that the whole index (all 11 bytes) is being used, with
a "range" type, so this is fast. I tried using a join as you
recommended, it gave me:
mysql> EXPLAIN SELECT * FROM accounts JOIN transactions ON account_id =
id AND date_stamp BETWEEN 1096588800001 AND 1099267200001 WHERE
canon_name = 'wms' AND state = 'active';
+--------------+------+-----------------------+------------+---------+-------------+------+-------------+
| table | type | possible_keys | key | key_len | ref |
rows | Extra |
+--------------+------+-----------------------+------------+---------+-------------+------+-------------+
| accounts | ref | PRIMARY,canon_name | canon_name | 11 | const,const |
1 | Using where |
| transactions | ref | date_stamp,account_id | account_id | 3 | accounts.id |
35 | Using where |
+--------------+------+-----------------------+------------+---------+-------------+------+-------------+
2 rows in set (0.03 sec)
So, same as the first case - it is just refusing to use a "range" type
of index, even though in some cases this would reduce the number of rows
that must be fetched by a factor of 10 or more.
On Fri, 2004-10-01 at 07:36, Michael Stassen wrote:
> Well, you haven't posted the output of EXPLAIN, but I'll take a guess. I
> expect mysql sees your query as having a JOIN condition of
> accounts.account_id = transactions.account_id and two WHERE conditions:
>
> WHERE accounts.name = 'dave'
> AND when BETWEEN '2004-02-05' AND '2004-02-10'
>
> The optimizer first considers the 2 WHERE conditions, looking for the one it
> believes will result in fewer rows. Presumably there is an index on
> accounts.name, but there is no usable index on `when`, as it doesn't come
> first in the multi-column index. Even if there were a usable index on
> `when`, I expect there would be fewer rows in accounts with the correct name
> than rows in transactions within the date range. So, the optimizer chooses
> accounts as the first table. For each row found in accounts with the right
> name, it matches that up with rows in transactions according to the JOIN
> condition, using the first part of the index. Finally, it applies the
> remaining WHERE condition on the results to filter the matching rows.
>
> This may be a case where Shawn's usual advice is the way to go.
> That is, change your join to an explicit join rather than an implicit join,
> and move all the relevant conditions to the ON clause. In other words, see if
>
> SELECT *
> FROM accounts
> JOIN transactions ON accounts.account_id = transactions.account_id
> AND when BETWEEN '2004-02-05' AND '2004-02-10'
> WHERE accounts.name = 'dave';
>
> does any better.
>
> Michael
Bill Shubert ([EMAIL PROTECTED])
signature.asc
Description: This is a digitally signed message part
