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