Web lists-archives.com

Re: ORDER BY not using index?




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 <savage@xxxxxxxxxxxxx>:
> 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