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
