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)
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!