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:
