Are these duplicate indexes?

MySQL & MariaDB Other MySQL® Questions
1

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!

2

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

3

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 |±------------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+