[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: ORDER BY not using index?
From:       "yoku ts." <yoku0825 () gmail ! com>
Date:       2015-07-18 15:46:44
Message-ID: CAHB5oTNywKN0ctaKDnhZXDAX2J8HKDoC9W+w2mJzBc56nkiOWw () mail ! gmail ! com
[Download RAW message or body]

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@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


[prev in list] [next in list] [prev in thread] [next in thread]