RE: Indexes strangeness

Try to run optimize table on those tables,  get the explain, now the result need to be equal...

> Date: Thu, 25 Feb 2016 01:48:48 +0200
> Subject: Indexes strangeness
> From: [email protected]
> To: [email protected]
> 
> Hi All,
> 
> Can someone please fill me in as what I am seeing here... I have two
> identical tables, with identical indexes, having different records.  Both
> tables have +- 15m records in it...
> 
> 
> mysql> EXPLAIN SELECT ArticleID, DateObtained, DateAccessed, TimesAccessed
> FROM IDXa ORDER BY DateAccessed LIMIT 100000;
> +----+-------------+----------+-------+---------------+-----------------+---------+------+--------+-------+
> | id | select_type | table    | type  | possible_keys | key             |
> key_len | ref  | rows   | Extra |
> +----+-------------+----------+-------+---------------+-----------------+---------+------+--------+-------+
> |  1 | SIMPLE      | IDXa | index | NULL          | idxDateAccessed | 5
>   | NULL | 100000 | NULL  |
> +----+-------------+----------+-------+---------------+-----------------+---------+------+--------+-------+
> 1 row in set (0,00 sec)
> 
> mysql> EXPLAIN SELECT ArticleID, DateObtained, DateAccessed, TimesAccessed
> FROM IDXb ORDER BY DateAccessed LIMIT 100000;
> +----+-------------+----------+------+---------------+------+---------+------+----------+----------------+
> | id | select_type | table    | type | possible_keys | key  | key_len | ref
>  | rows     | Extra          |
> +----+-------------+----------+------+---------------+------+---------+------+----------+----------------+
> |  1 | SIMPLE      | IDXb | ALL  | NULL          | NULL | NULL    | NULL |
> 15004858 | Using filesort |
> +----+-------------+----------+------+---------------+------+---------+------+----------+----------------+
> 1 row in set (0,00 sec)
> 
> 
> Tables:
> 
> 
> 
> mysql> SHOW CREATE TABLE IDXa\G
> *************************** 1. row ***************************
>        Table: IDXa
> Create Table: CREATE TABLE `IDXa` (
>   `ArticleID` varchar(32) NOT NULL,
>   `DateObtained` datetime NOT NULL,
>   `DateAccessed` datetime NOT NULL,
>   `TimesAccessed` int(5) unsigned NOT NULL,
>   PRIMARY KEY (`ArticleID`),
>   KEY `idxDateAccessed` (`DateAccessed`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> 1 row in set (0,00 sec)
> 
> mysql> SHOW INDEXES FROM IDXa;
> +----------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | Table    | Non_unique | Key_name        | Seq_in_index | Column_name  |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> Index_comment |
> +----------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | IDXa |          0 | PRIMARY         |            1 | ArticleID    | A
>     |    14086444 |     NULL | NULL   |      | BTREE      |         |
>         |
> | IDXa |          1 | idxDateAccessed |            1 | DateAccessed | A
>     |     1408644 |     NULL | NULL   |      | BTREE      |         |
>         |
> +----------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> 2 rows in set (0,00 sec)
> 
> mysql> SHOW CREATE TABLE IDXb\G
> *************************** 1. row ***************************
>        Table: IDXb
> Create Table: CREATE TABLE `IDXb` (
>   `ArticleID` varchar(32) NOT NULL,
>   `DateObtained` datetime NOT NULL,
>   `DateAccessed` datetime NOT NULL,
>   `TimesAccessed` int(5) unsigned NOT NULL,
>   PRIMARY KEY (`ArticleID`),
>   KEY `idxDateAccessed` (`DateAccessed`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> 1 row in set (0,00 sec)
> 
> mysql> SHOW INDEXES FROM IDXb;
> +----------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | Table    | Non_unique | Key_name        | Seq_in_index | Column_name  |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> Index_comment |
> +----------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | IDXb |          0 | PRIMARY         |            1 | ArticleID    | A
>     |    15007345 |     NULL | NULL   |      | BTREE      |         |
>         |
> | IDXb |          1 | idxDateAccessed |            1 | DateAccessed | A
>     |     1250612 |     NULL | NULL   |      | BTREE      |         |
>         |
> +----------+------------+-----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> 2 rows in set (0,00 sec)
> 
> 
> Thnx.
> 
> 
> -- 
> 
> Regards,
> Chris Knipe