MySQL Indexing VarChar

MySQLvarchar

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?

I am trying to have all my queries use the index as I'm running into high Handler_read_rnd and Handler_read_rnd_next values.

If you need any other info I can post it too.

Best Answer

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.