title: mysql query takes 145 seconds
body: <p>I have a a mysql db with myisam engine. Along with many other table I have this table "et" which has around 8137037 records.</p>
  
  <p>I have created indexes (individual index of column hname and pnum, it did not help much later created joint index of hname and pnum and it help execute within a second)such that queries as follows</p>
  
  <pre><code> select st from et where hname='name' and pnum='1' limit 1
  </code></pre>
  
  <p>execute fast (with in a second) but the problem is I must execute this query </p>
  
  <pre><code>select st from et where hname='name' and pnum='1' order by id limit 1
  </code></pre>
  
  <p>where id is the primary key of the table and this query sometimes take 145 seconds :(</p>
  
  <p>how can i resolve this issue?</p>
  
  <h3>table structure</h3>
  
  <pre><code>mysql&gt; describe et;
  +------------+-------------+------+-----+---------+----------------+
  | Field      | Type        | Null | Key | Default | Extra          |
  +------------+-------------+------+-----+---------+----------------+
  | id         | bigint(20)  | NO   | PRI | NULL    | auto_increment |
  | client_id  | int(11)     | YES  | MUL | NULL    |                |
  | down_id    | bigint(20)  | YES  | MUL | NULL    |                |
  | port_index | int(11)     | YES  |     | NULL    |                |
  | date1      | bigint(20)  | YES  | MUL | NULL    |                |
  | username   | varchar(50) | YES  |     | NULL    |                |
  | hname      | varchar(80) | YES  | MUL | NULL    |                |
  | pnum       | varchar(10) | YES  | MUL | NULL    |                |
  | status     | int(3)      | YES  | MUL | NULL    |                |
  | department | int(2)      | YES  | MUL | NULL    |                |
  | comments   | text        | YES  |     | NULL    |                |
  | issue      | int(1)      | YES  | MUL | NULL    |                |
  +------------+-------------+------+-----+---------+----------------+
  12 rows in set (0.06 sec)
  </code></pre>
  
  <h3>Indexes</h3>
  
  <pre><code>mysql&gt; show indexes from et;
  +-----+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  |Table| Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +-----+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | et  |          0 | PRIMARY        |            1 | id          | A         |     8137037 |     NULL | NULL   |      | BTREE      |         |
  | et  |          1 | status         |            1 | status      | A         |          12 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | down_id        |            1 | down_id     | A         |     4068518 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | issue_idx      |            1 | issue       | A         |     8137037 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | hname_idx      |            1 | hname       | A         |         283 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | pname_idx      |            1 | pnum        | A         |         136 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | clientid_idx   |            1 | client_id   | A         |     8137037 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | date1_idx      |            1 | date1       | A         |     8137037 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | department_idx |            1 | department  | A         |     2712345 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | hp_idx         |            1 | hname       | A         |         283 |     NULL | NULL   | YES  | BTREE      |         |
  | et  |          1 | hp_idx         |            2 | pnum        | A         |        4834 |     NULL | NULL   | YES  | BTREE      |         |
  +-----+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  11 rows in set (0.06 sec)
  </code></pre>
  
  <h3>Explain Plan</h3>
  
  <pre><code>mysql&gt; explain select status from et where hname='mmah' and port_num='1' limit 1;
  +----+-------------+------------+------+----------------------------------+--------------+---------+-------------+------+-------------+
  | id | select_type | table      | type | possible_keys                    | key          | key_len | ref         | rows | Extra       |
  +----+-------------+------------+------+----------------------------------+--------------+---------+-------------+------+-------------+
  |  1 | SIMPLE      | error_trap | ref  | hname_idx,pname_idx,hostport_idx | hostport_idx | 96      | const,const |   37 | Using where |
  +----+-------------+------------+------+----------------------------------+--------------+---------+-------------+------+-------------+
  1 row in set (0.02 sec)
  
  mysql&gt; explain select status from et where host_name='mmah' and pnum='1' order by id desc limit 1;
  +----+-------------+------------+------+----------------------------------+--------------+---------+-------------+------+-----------------------------+
  | id | select_type | table      | type | possible_keys                    | key          | key_len | ref         | rows | Extra                       |
  +----+-------------+------------+------+----------------------------------+--------------+---------+-------------+------+-----------------------------+
  |  1 | SIMPLE      | error_trap | ref  | hname_idx,pname_idx,hostport_idx | hostport_idx | 96      | const,const |   37 | Using where; Using filesort |
  +----+-------------+------------+------+----------------------------------+--------------+---------+-------------+------+-----------------------------+
  1 row in set (0.00 sec)
  </code></pre>