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
