mysql query takes 145 seconds

Question

I have a a mysql db with myisam engine. Along with many other table I have this table "et" which has around 8137037 records.

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

 select st from et where hname='name' and pnum='1' limit 1

execute fast (with in a second) but the problem is I must execute this query

select st from et where hname='name' and pnum='1' order by id limit 1

where id is the primary key of the table and this query sometimes take 145 seconds :(

how can i resolve this issue?

table structure

mysql> 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)

Indexes

mysql> 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)

Explain Plan

mysql> 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> 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)

Answer 1

Have you tried creating an index of (id, hname,pnum)?

If your index includes and is already ordered by ID then that effectively removes that concern.

There is no reason you cannot include ID in another index, just because it is also the primary.

Answer 2

Create an index in the table that contains hname and pnum.

This will cause MYSQL to create a shadow table, which only contains those 2 parameters, and whenever those 2 parmaters are used in a where, it looks in that shadow table, and finds them very fast.