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