I have 3 columns a,b and c and i have indexed them as (a,b,c). i have a query like this :
SELECT * FROM tablename WHERE a=something and c=someone
My question is Does this query use this index or not!?
-
3Use EXPLAIN and see for yourself.Mihai– Mihai2013-10-20 20:04:44 +00:00Commented Oct 20, 2013 at 20:04
Add a comment
|
2 Answers
It may use the first column (a) of the index, but it can't use the third column (c).
One way you can tell is that the output of EXPLAIN.
Here's an example:
mysql> create table tablename (a int, b int, c int, key (a,b,c));
...I filled it with some random data...
mysql> explain SELECT * FROM tablename WHERE a=125 and c=456\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tablename
type: ref
possible_keys: a
key: a
key_len: 5
ref: const
rows: 20
Extra: Using where; Using index
The above shows ref: const which shows only one of the constant values are used to find rows in the index. Also the key_len: 5 shows only a subset of the index is used, since an index entry with three integers should be larger than 5 bytes.
mysql> explain SELECT * FROM tablename WHERE a=125 and b = 789 and c=456\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tablename
type: ref
possible_keys: a
key: a
key_len: 15
ref: const,const,const
rows: 1
Extra: Using index
When we use conditions on all three columns, it shows ref: const,const,const showing that all three values are being used to look up index entries. And the key_len is large enough to be an entry of three integers.
5 Comments
Raymond Nijland
+1 nice explainment i've editted your post (needs to be reviewed) with an calculation why key len 5 pops up in the first explain and why key len 15 pops up in the second explain
Bill Karwin
@RaymondNijland, thanks, but you could have just added that as a comment. E.g. INT is 4 bytes, and each nullable column uses an additional bit per column for nullability. The EXPLAIN report rounds up, assuming you might be indexing columns across your table so each one would have its own nullability byte.
Raymond Nijland
i don't think the EXPLAIN report rounds up the ken_len value or do I misunderstand your comment right now.. if you run the query EXPLAIN SELECT * FROM tablename WHERE a=125 and b=456 your key_len should be 10 (2*4)(ints)+(2*1)(NULL) because a,b off compound index a,b,c can/will be used for this query..
Bill Karwin
@RaymondNijland, in fact it does report 10 bytes when it uses the (a,b) parts of the index. If we defined those columns as NOT NULL, it reports 4, 8, or 12 bytes. So there is 1 byte per column overhead when it's possible that the columns are NULL. I'm just not sure this is how the index is stored, or if it's just EXPLAIN that is rounding up the values.
Raymond Nijland
true.. the index will also contain an NULL byte indication or WHERE ... IS NULL or WHERE ... IS NOT NULL should always need an table scan.. And the EXPLAIN key_len is an "fixed number" and won't be rounded up
As Mihal says, if you prefix the query with EXPLAIN, the optimizer will tell you if it uses the index or not. Bill is partially correct in that it will only look up the value for a in the index, but if the table only contains the columns a,b and c, then the index is covering and the values for b and c will be retrieved from the index without reference to the table data - but the DBMS will still scan through all values of b and c in the index - not just going directly to the specified value for c.
It may be possible to fudge a query to make it use an index to a greater depth - assuming that b is an integer....
SELECT *
FROM tablename
WHERE a='something'
AND b BETWEEN -8388608 AND 8388607
AND c='someone'
1 Comment
Bill Karwin
That won't help -- the condition on
b is a range condition, which means it won't help find any specific value(s) for c.