# Are these duplicate indexes?

**URL:** <https://forums.percona.com/t/are-these-duplicate-indexes/1463>\
**Category:** Other MySQL® Questions\
**Created:** [August 4, 2010, 7:26am UTC](https://forums.percona.com/t/are-these-duplicate-indexes/1463 "2010-08-04T07:26:00Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![micromatikal](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@micromatikal](https://forums.percona.com/u/micromatikal)\
**Post date:** [August 4, 2010, 7:26am UTC](https://forums.percona.com/t/are-these-duplicate-indexes/1463/1 "2010-08-04T07:26:00Z")

</div>

I am checking out the indexes on some of our tables here (new environment to me) and am seeing things like:

±------------------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------+| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |±------------------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------+| stats\_daily\_part\_search | 0 | PRIMARY | 1 | sid | A | 1770954 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 0 | part\_date | 1 | part | A | NULL | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 0 | part\_date | 2 | date | A | 1770954 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | part | 1 | part | A | NULL | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | searches | 1 | searches | A | NULL | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | remote\_searches | 1 | remote\_searches | A | NULL | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | date | 1 | date | A | NULL | NULL | NULL | | BTREE | |±------------------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------+

Now, I notice that there are multiple indexes on part, and date for example, one in part\_date, and one in part for example. However they have different cardinality values sometimes, even though it appears to be a redundant index? Is this really the case? Can I eliminate one of the date and part indexes from the above example safely?

Thanks so much!

Tim

EDIT - I did an optimize on the table and now:

±------------------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------+| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment |±------------------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------+| stats\_daily\_part\_search | 0 | PRIMARY | 1 | sid | A | 1771614 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 0 | part\_date | 1 | part | A | 885807 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 0 | part\_date | 2 | date | A | 1771614 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | part | 1 | part | A | 885807 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | searches | 1 | searches | A | 3 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | remote\_searches | 1 | remote\_searches | A | 1 | NULL | NULL | | BTREE | || stats\_daily\_part\_search | 1 | date | 1 | date | A | 127 | NULL | NULL | | BTREE | |±------------------------±-----------±----------------±-------------±----------------±----------±------------±---------±-------±-----±-----------±--------+

Cardinality does match, so these are definitely duplicates of each other, correct?

NEW EDIT - Nevermind, confirmed that the part index is indeed a duplicate. Thanks!

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [August 4, 2010, 9:17am UTC](https://forums.percona.com/t/are-these-duplicate-indexes/1463/2 "2010-08-04T09:17:38Z")

</div>

Can you paste SHOW CREATE TABLE instead? It is much easier to read.

---

<div class="post-metadata">

**Author:** ![micromatikal](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@micromatikal](https://forums.percona.com/u/micromatikal)\
**Post date:** [August 4, 2010, 9:20am UTC](https://forums.percona.com/t/are-these-duplicate-indexes/1463/3 "2010-08-04T09:20:41Z")

</div>

Sure thing, is this what you’re hoping for?:

±------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table |±------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| stats\_daily\_part\_search | CREATE TABLE `stats_daily_part_search` ( `sid` bigint(20) unsigned NOT NULL AUTO\_INCREMENT, `part` varchar(255) NOT NULL DEFAULT ‘’, `searches` int(10) unsigned NOT NULL DEFAULT ‘0’, `remote_searches` int(10) NOT NULL DEFAULT ‘0’, `date` date NOT NULL DEFAULT ‘0000-00-00’, `spider_google` int(11) NOT NULL DEFAULT ‘0’, `spider_yahoo` int(11) NOT NULL DEFAULT ‘0’, `spider_other` int(11) NOT NULL DEFAULT ‘0’, PRIMARY KEY (`sid`), UNIQUE KEY `part_date` (`part`,`date`), KEY `part` (`part`), KEY `searches` (`searches`), KEY `remote_searches` (`remote_searches`), KEY `date` (`date`)) ENGINE=MyISAM AUTO\_INCREMENT=1774195 DEFAULT CHARSET=latin1 |±------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+