I have a performance/index usage problem, and I am hoping somebody can tell me why this problem exists, and whether there is a better solution than the workaround that I'm using now.
The problem: I have a table with a two column index, such as
CREATE TABLE transactions (
account_id INT NOT NULL,
when DATETIME NOT NULL,
INDEX(account_id, when)
);
I frequently need to do selections like:
SELECT * FROM accounts, transactions
WHERE accounts.name = 'dave'
AND accounts.account_id = transactions.account_id
AND when BETWEEN '2004-02-05' AND '2004-02-10';
If "dave" has a huge number of transactions (and some of the accounts
do), this runs very slowly. EXPLAIN tells me that only the account_id
part of the "transactions" index is being used, with the "ref" index
usage type - apparently, every single one of dave's transactions is
being examined, and the "when BETWEEN ..." part of the join is only
being looked at after fetching the rows, even though the index itself
had all the data needed!
If I do this query in two parts, like this:
SELECT account_id FROM accounts WHERE name = 'dave';
SELECT * FROM transactions
WHERE account_id = <dave's account ID>
AND when BETWEEN '2004-02-05' AND '2004-02-10';
Suddenly the performance is great, and the full index is used, with a
"range" type of index usage. The MySQL documentation says that the
"range" type can only be used on constants - but I'm wondering, why does
it count this as non-constant? The dates I'm using are constant, so it
seems that it should be very easy for mysql to do it fast all in one
query. Only the first column of the index is variable.
I thought maybe the "range" index type didn't work when there could be
multiple hits on the first half of the index, but "SELECT * FROM
transactions WHERE account_id IN (3, 10, 50) AND when BETWEEN ..." also
uses the "range" index type and runs very fast!
So, can anybody mysql can't do "the right thing" with the
single-statement query? And is there a better option for me than to do
it in two queries? I'm using MySql 4.0.x, I was curious about whether
using subqueries in 4.1.x would help, but the point is moot right now
since my production systems won't be using 4.1.x until it leaves gamma
testing.
Bill Shubert ([EMAIL PROTECTED])
signature.asc
Description: This is a digitally signed message part
