title: Mysql query optimization Multi Column Index solves this slowness?
body: <pre><code>+----------------------------+------------------------------------------------------------------------------+------+-----+---------+----------------+
  | Field                      | Type                                                                         | Null | Key | Default | Extra          |
  +----------------------------+------------------------------------------------------------------------------+------+-----+---------+----------------+
  | type                       | enum('Website','Facebook','Twitter','Linkedin','Youtube','SeatGeek','Yahoo') | NO   | MUL | NULL    |                |
  | name                       | varchar(100)                                                                 | YES  | MUL | NULL    |                |
  | processing_interface_id    | bigint(20)                                                                   | YES  | MUL | NULL    |                |
  | processing_interface_table | varchar(100)                                                                 | YES  | MUL | NULL    |                |
  | create_time                | datetime                                                                     | YES  | MUL | NULL    |                |
  | run_time                   | datetime                                                                     | YES  | MUL | NULL    |                |
  | completed_time             | datetime                                                                     | YES  | MUL | NULL    |                |
  | reserved                   | int(10)                                                                      | YES  | MUL | NULL    |                |
  | params                     | text                                                                         | YES  |     | NULL    |                |
  | params_md5                 | varchar(100)                                                                 | YES  | MUL | NULL    |                |
  | priority                   | int(10)                                                                      | YES  | MUL | NULL    |                |
  | id                         | bigint(20) unsigned                                                          | NO   | PRI | NULL    | auto_increment |
  | status                     | varchar(40)                                                                  | NO   | MUL | none    |                |
  +----------------------------+------------------------------------------------------------------------------+------+-----+---------+----------------+
  
  select *  from remote_request use index ( processing_order )  where remote_request.status = 'none' and type = 'Facebook' and reserved = '0' order by priority desc limit 0, 40;
  </code></pre>
  
  <p>This table receives an extremely large amount of writes and reads. each remote_request ends up being a process, which can spawn anywhere between 0 and 5 other remote_requests depending on the type of request, and what the request does.  </p>
  
  <p>The table is currently sitting at about 3.5 Million records, and it goes to a snail pace when the site itself is under heavy load and I have more then 50 or more instances running simultaneously. (REST requests are the purpose of the table just in case you were not sure).</p>
  
  <p>As the table grows it just gets worse and worse.  I can clear the processed requests out on a daily basis but ultimatly this is not fixing the problem.</p>
  
  <p>What I need is for this query to always have a very low response ratio.  </p>
  
  <p>Here are the current indexes on the table.</p>
  
  <pre><code>+----------------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | Table          | Non_unique | Key_name                         | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
  +----------------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  | remote_request |          0 | PRIMARY                          |            1 | id                         | A         |     2403351 |     NULL | NULL   |      | BTREE      |         |               |
  | remote_request |          1 | type_index                       |            1 | type                       | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
  | remote_request |          1 | processing_interface_id_index    |            1 | processing_interface_id    | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | processing_interface_table_index |            1 | processing_interface_table | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | create_time_index                |            1 | create_time                | A         |      160223 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | run_time_index                   |            1 | run_time                   | A         |      343335 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | completed_time_index             |            1 | completed_time             | A         |      267039 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | reserved_index                   |            1 | reserved                   | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | params_md5_index                 |            1 | params_md5                 | A         |     2403351 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | priority_index                   |            1 | priority                   | A         |         716 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | status_index                     |            1 | status                     | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
  | remote_request |          1 | name_index                       |            1 | name                       | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | processing_order                 |            1 | priority                   | A         |         200 |     NULL | NULL   | YES  | BTREE      |         |               |
  | remote_request |          1 | processing_order                 |            2 | status                     | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
  | remote_request |          1 | processing_order                 |            3 | type                       | A         |         200 |     NULL | NULL   |      | BTREE      |         |               |
  | remote_request |          1 | processing_order                 |            4 | reserved                   | A         |         200 |     NULL | NULL   | YES  | BTREE      |         |               |
  +----------------+------------+----------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
  </code></pre>
  
  <p>Any idea how i solve this? Is it not possible to make some sort of complicated index that would automatic order them with priority, then take the first 40 that match the 'Facebook' type?  It currently is scanning more then 500k rows of the table before it returns a result which is grossly inefficient.</p>
  
  <p>Some other version of the query that I have been tinkering with are:</p>
  
  <pre><code>select *  from remote_request use index ( type_index,status_index,reserved_index,priority_index )  where remote_request.status = 'none' and type = 'Facebook' and reserv                          ed = '0' order by priority desc limit 0, 40
  </code></pre>
  
  <p>It would be amazing if we could get the rows scanned to under 1000 rows depending on just how many types of requests enter the table.</p>
  
  <p>Thanks in advance, this might be a real nutcracker for most except the most experienced mysql experts?</p>