GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Are these duplicate indexes?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Are these duplicate indexes?

    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.

    Comment


    • #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 |+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

      Comment


      • #4
        There is duplication:

        UNIQUE KEY `part_date` (`part`,`date`),
        KEY `part` (`part`),

        The part key is made redundant by part_date. Everything else is fine.

        Comment

        Working...
        X