OK, little puzzled by this one, wondering if my expectations of behavior
are off. (Apologies for the longish post..)
This is with 3.2.7. I have a reasonably simple 'users' table, with a
single compound index (at first) on user last/first name:
create table users (
id integer primary key,
account_id integer not null,
first_name varchar collate nocase,
last_name varchar collate nocase,
enabled date default CURRENT_TIMESTAMP,
expires date default null,
deleted integer
);
create index user_name_index on users(last_name, first_name);
Then ran the following query:
SELECT id, first_name, last_name
FROM users
WHERE account_id = ? AND last_name >= ? AND last_name < ? AND deleted is
null
ORDER BY last_name, first_name
LIMIT 25 OFFSET ? ;
which performs well enough. Thinking that adding an index to account_id
would only speed things up, I added the following:
create index user_account_index on users(account_id);
Which made the following query take 10x longer (!) to produce the same
results. This is what left me scratching my head. I ran explain both
before and after the addition of the index and got the following
(totally different) outputs:
Without user_account_index:
0|Noop|0|0|
1|Integer|25|0|
2|MustBeInt|0|0|
3|Negative|0|0|
4|MemStore|0|1|
5|Variable|4|0|
6|MustBeInt|0|0|
7|Negative|0|0|
8|MemStore|1|1|
9|Goto|0|50|
10|Integer|0|0|
11|OpenRead|0|2|
12|SetNumColumns|0|7|
13|Integer|0|0|
14|OpenRead|2|3|keyinfo(2,NOCASE,NOCASE)
15|Variable|3|0|
16|NotNull|-1|19|
17|Pop|1|0|
18|Goto|0|47|
19|MakeRecord|1|0|tt
20|MemStore|3|1|
21|Variable|2|0|
22|NotNull|-1|25|
23|Pop|1|0|
24|Goto|0|47|
25|MakeRecord|1|0|tt
26|MoveGe|2|47|
27|MemLoad|3|0|
28|IdxGE|2|47|
29|RowKey|2|0|
30|IdxIsNull|1|46|
31|IdxRowid|2|0|
32|MoveGe|0|0|
33|Column|0|1|
34|Variable|1|0|
35|Ne|361|46|collseq(BINARY)
36|Column|0|6|
37|NotNull|1|46|
38|MemIncr|1|0|
39|IfMemPos|1|41|
40|Goto|0|46|
41|MemIncr|0|47|
42|Rowid|0|0|
43|Column|0|2|
44|Column|0|3|
45|Callback|3|0|
46|Next|2|27|
47|Close|0|0|
48|Close|2|0|
49|Halt|0|0|
50|Transaction|0|0|
51|VerifyCookie|0|2|
52|Goto|0|10|
53|Noop|0|0|
After adding user_account_index:
0|OpenVirtual|1|4|keyinfo(2,NOCASE,NOCASE)
1|Integer|25|0|
2|MustBeInt|0|0|
3|Negative|0|0|
4|MemStore|0|1|
5|Variable|4|0|
6|MustBeInt|0|0|
7|Negative|0|0|
8|MemStore|1|1|
9|Goto|0|64|
10|Integer|0|0|
11|OpenRead|0|2|
12|SetNumColumns|0|7|
13|Integer|0|0|
14|OpenRead|2|4|keyinfo(1,BINARY)
15|Variable|1|0|
16|NotNull|-1|19|
17|Pop|1|0|
18|Goto|0|47|
19|MakeRecord|1|0|i
20|MemStore|2|0|
21|MoveGe|2|47|
22|MemLoad|2|0|
23|IdxGE|2|47|+
24|RowKey|2|0|
25|IdxIsNull|1|46|
26|IdxRowid|2|0|
27|MoveGe|0|0|
28|Column|0|3|
29|Variable|2|0|
30|Lt|372|46|collseq(NOCASE)
31|Column|0|3|
32|Variable|3|0|
33|Ge|372|46|collseq(NOCASE)
34|Column|0|6|
35|NotNull|1|46|
36|Rowid|0|0|
37|Column|0|2|
38|Column|0|3|
39|MakeRecord|3|0|
40|Column|0|3|
41|Column|0|2|
42|Sequence|1|0|
43|Pull|3|0|
44|MakeRecord|4|0|
45|IdxInsert|1|0|
46|Next|2|22|
47|Close|0|0|
48|Close|2|0|
49|Sort|1|63|
50|MemIncr|1|0|
51|IfMemPos|1|53|
52|Goto|0|62|
53|MemIncr|0|63|
54|Column|1|3|
55|Integer|3|0|
56|Pull|1|0|
57|Column|-1|0|
58|Column|-2|1|
59|Column|-3|2|
60|Callback|3|0|
61|Pop|2|0|
62|Next|1|50|
63|Halt|0|0|
64|Transaction|0|0|
65|VerifyCookie|0|3|
66|Goto|0|10|
67|Noop|0|0|
An "explain query plan" of the second also gave me something interesting:
0|0|TABLE users WITH INDEX user_account_index
Does this mean it's only able to use one index, so the addition of a
separate index knocks the most useful one out of the running?
Anyone have any thoughts on why adding an index nailed the
plan/performance so remarkably?
Thanks,
-P
