title: MySQL Query Limitation on OR and IN clauses
body: <p>We have a pricing table which store the pricing information indexed against four attributes(store_id, fee_type, business_line, variant).</p>
  <pre><code>CREATE TABLE `pricing_info` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `business_line` varchar(255) NOT NULL,
    `store_id` bigint NOT NULL,
    `fee_type` varchar(255) NOT NULL,
    `variant` varchar(255) NOT NULL,
    `fee_config` text,
    `created_at` datetime NOT NULL,
    `created_by` varchar(255) NOT NULL,
    `updated_at` datetime DEFAULT NULL,
    `updated_by` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `store_fee_business_variant` (`store_id`,`fee_type`,`business_line`,`variant`)
  ) ENGINE=InnoDB AUTO_INCREMENT=2268 DEFAULT CHARSET=latin1
  </code></pre>
  <p>In the Application layer, we get call for multiple store-id which is translated to sample query like this to DB:</p>
  <p>Query using IN clauses:</p>
  <pre><code>select * from pricing_info where store_id IN (store_id1, store_id2, ......, store_id400) and fee_type = 'Distance' and business_line = 'Default';
  </code></pre>
  <p>Query using OR clauses:</p>
  <pre><code>select * from pricing_info where 
  (store_id = store_id1 and fee_type = 'Distance' and business_line = 'Default') OR
  (store_id = store_id2 and fee_type = 'Distance' and business_line = 'Default') OR
  ......
  (store_id = store_id400 and fee_type = 'Distance' and business_line = 'Default');
  </code></pre>
  <p>Problem:
  In the OR/IN clauses index(store_fee_business_variant) is getting used till 400 store_ID, but beyond that it goes to full table scan. So, question is how does MySQL decides when to move from Index to Full table scan based on the size of the query?</p>
  <p>Additional Info for the table and index:<br />
  Number of rows(pricing_info): 2266<br />
  Size of index: 0.16 MB (by querying mysql.innodb_index_stats)<br />
  Number of pages in index: 10 (by querying mysql.innodb_index_stats)<br />
  Number of leaf pages in index: 9 (by querying mysql.innodb_index_stats)</p>