Hi, Your query have to access all rows in `myTable`, thus MySQL optimizer guesses "reading sequentially is faster than working through an index". http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html
The case of not using index, * Reading whole myTable.MYD sequentially * Sorting 443k rows The case of using index, * Reading all of idx_DateAccessed * Reading whole myTable.MYD *randomly* MySQL would like to avoid randomly scan, maybe. You can avoid filesort with FORCE INDEX, as you tell. Regards, 2015-07-18 16:32 GMT+09:00 Chris Knipe <sav...@savage.za.org>: > Hi, > > Can someone perhaps assist with the below... I'm not sure at all why my > index aren't being used for the ORDER BY. Currently some 443K records in > the table, but this will grow to a good few million. I simply cannot, > afford a filesort. > > > mysql> SELECT COUNT(*) FROM myTable; > +----------+ > | COUNT(*) | > +----------+ > | 443808 | > +----------+ > 1 row in set (0.00 sec) > > mysql> EXPLAIN SELECT * FROM myTable ORDER BY DateAccessed; > +----+-------------+--------------+------+---------------+------+---------+------+--------+----------------+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > +----+-------------+--------------+------+---------------+------+---------+------+--------+----------------+ > | 1 | SIMPLE | myTable | ALL | NULL | NULL | NULL | NULL > | 443808 | Using filesort | > +----+-------------+--------------+------+---------------+------+---------+------+--------+----------------+ > 1 row in set (0.00 sec) > > mysql> EXPLAIN SELECT * FROM myTable FORCE INDEX (idx_DateAccessed) ORDER > BY DateAccessed; > +----+-------------+--------------+-------+---------------+------------------+---------+------+--------+-------+ > | id | select_type | table | type | possible_keys | key > | key_len | ref | rows | Extra | > +----+-------------+--------------+-------+---------------+------------------+---------+------+--------+-------+ > | 1 | SIMPLE | myTable | index | NULL | idx_DateAccessed | 4 > | NULL | 443808 | | > +----+-------------+--------------+-------+---------------+------------------+---------+------+--------+-------+ > 1 row in set (0.00 sec) > > mysql> SHOW CREATE TABLE myTable; > +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | Table | Create Table > > > > | > +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | myTable | CREATE TABLE `myTable` ( > `ArticleID` char(32) NOT NULL, > `DateObtained` int(10) unsigned NOT NULL, > `DateAccessed` int(10) unsigned NOT NULL, > `TimesAccessed` int(10) unsigned NOT NULL, > PRIMARY KEY (`ArticleID`), > KEY `idx_DateAccessed` (`DateAccessed`) USING BTREE > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 | > +--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > 1 row in set (0.00 sec) > > mysql> SHOW INDEX FROM myTable; > +--------------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name > | Collation | Cardinality | Sub_part | Packed | Null | Index_type | > Comment | Index_comment | > +--------------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ > | myTable | 0 | PRIMARY | 1 | ArticleID | A > | 443808 | NULL | NULL | | BTREE | | > | > | myTable | 1 | idx_DateAccessed | 1 | DateAccessed | A > | 147936 | NULL | NULL | | BTREE | | > | > +--------------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ > 2 rows in set (0.00 sec) > > > -- > > Regards, > Chris Knipe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
