title: Mysql strange behaviour on querying tinyint boolean field
body: <p>I have a mysql table <code>voucher_codes</code> with the following schema:</p>
  
  <pre><code>mysql&gt; describe  voucher_codes;
  +--------------------+------------------+------+-----+---------+----------------+
  | Field              | Type             | Null | Key | Default | Extra          |
  +--------------------+------------------+------+-----+---------+----------------+
  | id                 | int(11)          | NO   | PRI | NULL    | auto_increment |
  | user_id            | int(10) unsigned | YES  | MUL | NULL    |                |
  | promo_code         | varchar(30)      | YES  | MUL | NULL    |                |
  | voucher_value      | double           | NO   |     | NULL    |                |
  | date_created       | datetime(6)      | NO   |     | NULL    |                |
  | date_modified      | datetime(6)      | NO   |     | NULL    |                |
  | redeem_flag        | tinyint(1)       | NO   |     | NULL    |                |
  | fk_voucher_rule_id | int(11)          | NO   | MUL | NULL    |                |
  +--------------------+------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>The indexes defined on the table are as follows:</p>
  
  <pre><code>mysql&gt; show index  from voucher_codes;
  +---------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table         | Non_unique | Key_name                 | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +---------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | voucher_codes |          0 | PRIMARY                  |            1 | id                 | A         |    15280692 |     NULL | NULL   |      | BTREE      |         |               |
  | voucher_codes |          1 | voucher_codes_user_id    |            1 | user_id            | A         |     2805369 |     NULL | NULL   | YES  | BTREE      |         |               |
  | voucher_codes |          1 | voucher_codes_promo_code |            1 | promo_code         | A         |     7389780 |     NULL | NULL   | YES  | BTREE      |         |               |
  | voucher_codes |          1 | fk_voucher_rule_id       |            1 | fk_voucher_rule_id | A         |          60 |     NULL | NULL   |      | BTREE      |         |               |
  +---------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </code></pre>
  
  <p>The numbers of records with voucher_code 1 and redeem_flag as 0 and 1 are as follows:</p>
  
  <pre><code>mysql&gt; select count(*) from voucher_codes  where redeem_flag =0 and fk_voucher_rule_id=1;
  +----------+
  | count(*) |
  +----------+
  |   135114 |
  +----------+
  1 row in set (3.17 sec)
  
  mysql&gt; select count(*) from voucher_codes  where redeem_flag =1 and fk_voucher_rule_id=1;
  +----------+
  | count(*) |
  +----------+
  |  1575024 |
  +----------+
  1 row in set (2.56 sec)
  </code></pre>
  
  <p>There are 1.5+ million records with <code>redeem_flag</code> as <code>1</code> and 100k+ are <code>0</code>.</p>
  
  <p>But the query with <code>redeem_flag</code> as <code>1</code> takes less time as compared with query with <code>redeem_flag</code> 0.
  Below are the results:</p>
  
  <pre><code>mysql&gt; select promo_code from voucher_codes  where redeem_flag =0 and fk_voucher_rule_id=1  limit 1 ;
  +--------------+
  | promo_code   |
  +--------------+
  | XXXXXXXXXX   |
  +--------------+
  1 row in set (3.67 sec)
  
  mysql&gt; explain select promo_code from voucher_codes  where redeem_flag =0 and fk_voucher_rule_id=1  limit 1 ;
  +----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
  | id | select_type | table         | partitions | type | possible_keys      | key                | key_len | ref   | rows    | filtered | Extra       |
  +----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
  |  1 | SIMPLE      | voucher_codes | NULL       | ref  | fk_voucher_rule_id | fk_voucher_rule_id | 4       | const | 3258352 |    10.00 | Using where |
  +----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)
  
  </code></pre>
  
  <p>Query with <code>redeem_flag</code> as 1:</p>
  
  <pre><code>mysql&gt; select promo_code from voucher_codes  where redeem_flag =1 and fk_voucher_rule_id=1  limit 1 ;
  +------------+
  | promo_code |
  +------------+
  | XXXXXXXXX  |
  +------------+
  1 row in set (0.00 sec)
  
  mysql&gt; explain select promo_code from voucher_codes  where redeem_flag =1 and fk_voucher_rule_id=1  limit 1 ;
  +----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
  | id | select_type | table         | partitions | type | possible_keys      | key                | key_len | ref   | rows    | filtered | Extra       |
  +----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
  |  1 | SIMPLE      | voucher_codes | NULL       | ref  | fk_voucher_rule_id | fk_voucher_rule_id | 4       | const | 3258352 |    10.00 | Using where |
  +----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
  1 row in set, 1 warning (0.00 sec)
  </code></pre>
  
  <p>Unable to understand this behaviour. Even though the index <code>fk_voucher_rule_id</code> is getting used, but it is taking more time with records which are more as compared to less number of records.</p>
  
  <p>Update: Adding show create table result:</p>
  
  <pre><code>mysql&gt; show create table voucher_codes;
  +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
  +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | voucher_codes | CREATE TABLE `voucher_codes` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(10) unsigned DEFAULT NULL,
    `promo_code` varchar(30) DEFAULT NULL,
    `voucher_value` double NOT NULL,
    `date_created` datetime(6) NOT NULL,
    `date_modified` datetime(6) NOT NULL,
    `redeem_flag` tinyint(1) NOT NULL,
    `fk_voucher_rule_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `voucher_codes_user_id` (`user_id`),
    KEY `voucher_codes_promo_code` (`promo_code`),
    KEY `fk_voucher_rule_id` (`fk_voucher_rule_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=16305657 DEFAULT CHARSET=latin1 |
  +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  </code></pre>