MySQL Indexing VarChar

I am trying to index my blogentries database for better performance but found an issue.

Here is the structure:

CREATE TABLE IF NOT EXISTS `blogentries` (
  `id_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title_id` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `entry_id` varchar(5000) COLLATE latin1_german2_ci NOT NULL,
  `date_id` int(11) NOT NULL,
  PRIMARY KEY (`id_id`)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1
COLLATE=latin1_german2_ci
AUTO_INCREMENT=271;

A query like the following uses the index properly:

EXPLAIN SELECT id_id,title_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | blogentries | index | NULL | PRIMARY | 114 | NULL | 126 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

However, when I add the entry_id into the SELECT query it uses the filesort

EXPLAIN SELECT id_id,title_id,entry_id FROM blogentries ORDER by id_id DESC

+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | blogentries | ALL | NULL | NULL | NULL | NULL | 126 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+

I was wondering why this is happening and how I can avoid it? Is it due to the VarChar, and that should be changed to something else

Since you don't have a WHERE clause in either query, you're returning all rows in both cases, so I'd think the use or non-use of the index would have very little impact on performance in these examples.

StackExchange Database Administrators Q#27760, answer score: 7