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