The Mysql query does not use all indexed columns 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_idcolumn is used for searching??

---

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 INTwould 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.)