Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My query is not using indexes. It is a simple query. Can someone explain why that's the case?

The query:

select TIME_FORMAT(start_time,'%H:%i') start_time,
       TIME_FORMAT(end_time,'%H:%i') end_time,
       rule_id, day_of_week, long_message,
       short_message, rotation_weight, mode
from timed_delivery_messages order by rule_id asc;

Explain Plan:

| id | select_type | table                   | type  | possible_keys | key     | key_len | ref  | rows | Extra |
|  1 | SIMPLE      | timed_delivery_messages | index | NULL          | PRIMARY | 4       | NULL |   22 |       |
1 row in set (0.00 sec)


mysql> show create table timed_delivery_messages\G
*************************** 1. row ***************************
       Table: timed_delivery_messages
Create Table: CREATE TABLE `timed_delivery_messages` (
  `row_create` datetime DEFAULT NULL,
  `row_mod` datetime DEFAULT NULL,
  `rule_id` int(11) NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `day_of_week` varchar(7) DEFAULT NULL,
  `rotation_weight` varchar(50) DEFAULT NULL,
  `mode` varchar(10) DEFAULT 'active',
  `long_message` varchar(256) DEFAULT NULL,
  `short_message` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`rule_id`)
1 row in set (0.00 sec)
share|improve this question
With 22 rows in the table, why do you care if an index is used or not? And even if the table had 22 zillion rows, the query requests for all of them. The table has to be read, all of it. – ypercubeᵀᴹ Apr 23 '13 at 9:14

As far as I can tell, your query is using an index, the one that backs your primary key.

Look at the EXPLAIN Output Format documentation for the "join type" column. The second bullet point for the index join type is what your select is using:

  • A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.