mysql index not optimizing query

Question

I have mysql MyISAM table on which I am doing a simple select id from mytable limit 1;. This just freezes the system.

I tried explain select id from mytable limit 1;. Again it freezes my system. Table demographics: 50k records, 10 mbs size, 2 indexes (primary key autoincrement), 8 columns.

I am clueless why the explain statement failed, as it is supposed to display the query plan, nothing else. Neither the table size is enormous nor the number of records, then why is mysql working so slow? Rather, what am I missing here?

As an aside: what is your query supposed to do? Results will be indeterminate without an `ORDER BY` clause... — eggyal, Jun 10 '12 at 05:45
@eggyal Lets make it `order by id desc`. But this query too freezes. I have another table will just 100 records in it and it works. — jerrymouse, Jun 10 '12 at 05:50
Whilst one connection is running such a frozen query, what does another connection show for that connection under `SHOW PROCESSLIST`? — eggyal, Jun 10 '12 at 05:52
Hmm... with a `limit 1` it should return "instantly", index or not... something *else* is likely broken. (Event a 50k result set without a view operation should start streaming right away -- and not "freeze the system".) — , Jun 10 '12 at 05:55

score 0 · Accepted Answer · edited May 23 '17 at 11:45

It was due a waiting state on mytable. eggyal gave me the clue to use show processlist. It showed:

+-----+---------+-----------------+----------------+---------+------+---------------------------------+----------------------------------------------------+
| Id  | User    | Host            | db             | Command | Time | State                           | Info                                               |
+-----+---------+-----------------+----------------+---------+------+---------------------------------+----------------------------------------------------+
| 349 | root    | localhost:56612 | mydb           | Query   | 3582 | Waiting for table metadata lock | ALTER TABLE `mytable` ADD INDEX(`fk_to_02`) |

I planted a kill 349 to terminate that wait chain, and now the explain statement works as expected.

mysql index not optimizing query

1 Answers1