Take the 2-minute tour ×
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. It's 100% free, no registration required.

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)

Table:

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.