Web lists-archives.com

Indexes strangeness




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