CODEWITHSUNDEEP
mysqlindexing
user2274074
user2274074

Reputation: 1087

Mysql query not using all indexed column for searching

For faster search i have indexed two columns(composite index) client_id and batch_id.

Below is my output of indexes of my table

show indexes from authentication_codes

*************************** 3. row ***************************
        Table: authentication_codes
   Non_unique: 1
     Key_name: client_id
 Seq_in_index: 1
  Column_name: client_id
    Collation: A
  Cardinality: 18
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: authentication_codes
   Non_unique: 1
     Key_name: client_id
 Seq_in_index: 2
  Column_name: batch_id
    Collation: A
  Cardinality: 18
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
4 rows in set (0.02 sec)

ERROR:
No query specified

when i use explain to check if indexing is used in query or not it gives me below output.

mysql> explain select * from authentication_codes where client_id=6 and batch_id="101" \G;


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: authentication_codes
         type: ref
possible_keys: client_id
          key: client_id
      key_len: 773
          ref: const,const
         rows: 1044778
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

********************EDIT***************************

output of show create table authentication_codes is as below

mysql> show create table authentication_codes \G;
*************************** 1. row ***************************
       Table: authentication_codes
Create Table: CREATE TABLE `authentication_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `batch_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `serial_num` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_authentication_codes_on_code` (`code`),
  KEY `client_id_batch_id` (`client_id`,`batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=48406205 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

my question is why batch_id column is not used for searching. why only client_id column is used for searching??

Upvotes: 0

Views: 77

Answers (2)

Rick James
Rick James

Reputation: 142296

The EXPLAIN does not match the CREATE TABLE, at least in the name of the relevant index.

Explaining the EXPLAIN (as displayed at the moment):

  select_type: SIMPLE
        table: authentication_codes
         type: ref
possible_keys: client_id
          key: client_id    -- The index named "client_id" was used
      key_len: 773          -- (explained below)
          ref: const,const  -- 2 constants were used for the first two columns in that index
         rows: 1044778      -- About this many rows (2% of table) matches those two constants
        Extra: Using where

773 = 2 + 3 * 255 + 1 + 4 + 1
2 = length for VARCHAR
3 = max width of a utf8 character -- do you really need utf8?
255 = max length provided in VARCHAR(255) -- do you really need that much?
1 = extra length for NULL -- perhaps your columns could/should be NOT NULL?
4 = length of INT for client_id -- if you don't need 4 billion ids, maybe a smaller INT would work? and maybe UNSIGNED, too?

So, yes, it is using both parts of client_id=6 and batch_id="101". But there are a million rows in that batch for that client, so the query takes time.

If you want to discuss how to further speed up the use of this table, please provide the other common queries. (I don't want to tweak the schema to make this query faster, only to find that other queries are made slower.)

Upvotes: 0

Māris Kiseļovs
Māris Kiseļovs

Reputation: 17295

To use index on two columns you need to create two column index. MySQL cannot use two separate indexes on one table.

This query will add multi column index on client_id and batch_id

alter table authentication_codes add index client_id_batch_id (client_id,batch_id)

http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

Upvotes: 1

Related Questions