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.
WHERE 1=1
to your second query.SELECT @@sort_buffer_size
)?