title: selecting million rows is slow even for simple select statement
body: <p>I have simple table with partition(range partition 10 in number)</p>
  
  <pre><code>CREATE TABLE `document_key_points` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `key_point_id` int(11) DEFAULT NULL,
    `data_date` date DEFAULT NULL,
    `data_decimal` decimal(22,6) DEFAULT '0.000000',
    `data_boolean` tinyint(1) DEFAULT NULL,
    `document_id` int(11) DEFAULT NULL,
    `data_integer` int(11) DEFAULT NULL,
    `is_deleted` tinyint(1) DEFAULT '0',
    `data_string` text,
    `created_at` datetime DEFAULT NULL,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (`id`,`key_point_id`),
    KEY `data_integer` (`data_integer`),
    KEY `document_id` (`document_id`),
    KEY `key_point_id` (`key_point_id`),
    KEY `data_boolean` (`data_boolean`),
    KEY `data_decimal` (`data_decimal`),
    KEY `data_date` (`data_date`),
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  PARTITION BY RANGE (key_point_id) (
  PARTITION p0 VALUES LESS THAN (163),
  PARTITION p1 VALUES LESS THAN (271),
   PARTITION p2 VALUES LESS THAN (364),
   PARTITION p3 VALUES LESS THAN (370), 
   PARTITION p4 VALUES LESS THAN (378),
   PARTITION p5 VALUES LESS THAN (384), 
   PARTITION p6 VALUES LESS THAN (397),
   PARTITION p7 VALUES LESS THAN (460), 
   PARTITION p8 VALUES LESS THAN (487),
   PARTITION p9 VALUES LESS THAN (MAXVALUE));
  </code></pre>
  
  <p>I am running a simple select query and it is taking lot of time(12 sec) to execute </p>
  
  <pre><code>select data_string,document_id from document_key_points cd where key_point_id =12
  </code></pre>
  
  <p><strong>Explain</strong></p>
  
  <pre><code>+----+-------------+-------+------+---------------+---------+---------+-------+---------+-------------+
  | id | select_type | table | type | possible_keys | key      | key_len | ref   | rows    | Extra       |
  +----+-------------+-------+------+---------------+----------+---------+-------+---------+-------------+
  |  1 | SIMPLE      | cd    | ref  | key_pt_id     | key_pt_id| 4       | const | 1957136 | Using where |
  +----+-------------+-------+------+---------------+----------+---------+-------+---------+-------------+
  </code></pre>
  
  <p>I have 50 million rows in this table and the aim is to optimize the query  output close to 1-2 secs,
  what approach will help me to optimize this query to reach 1-2 secs?</p>
  
  <p>NOTE:same query is running in 8 sec without partitioning.</p>
  
  <p>UPDATE:
  Adding explain partitions</p>
  
  <pre><code>+----+-------------+-------+------------+------+---------------+-----------+---------+-------+---------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows    | Extra       |
  +----+-------------+-------+------------+------+---------------+-----------+---------+-------+---------+-------------+
  |  1 | SIMPLE      | cd    | p0         | ref  | key_pt_id     | key_pt_id | 4       | const | 1957136 | Using where |
  +----+-------------+-------+------------+------+---------------+-----------+---------+-------+---------+-------------+
  </code></pre>