CODEWITHSUNDEEP
mysqlindexing
Hadi Alizadeh Jalali
Hadi Alizadeh Jalali

Reputation: 300

Index of three columns in mySQL

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!?

Upvotes: 2

Views: 124

Answers (2)

symcbean
symcbean

Reputation: 48357

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'

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 4

Related Questions