Details

      Description

      #1 Read http://www.mysqlperformanceblog.com/2006/08/17/duplicate-indexes-and-redundant-indexes/
      #2 Run the relevant maatkit script:

      mk-duplicate-key-checker -h localhost -d <ezpublish-db> -u <ezpublish-user> --ask-pass
      

      To discover:

      # ########################################################################
      # ezpublish.ezcontentobject_attribute                                     
      # ########################################################################
       
      # ezcontentobject_attribute_contentobject_id is a left-prefix of ezcontentobject_attribute_co_id_ver_lang_code
      # Key definitions:
      #   KEY `ezcontentobject_attribute_contentobject_id` (`contentobject_id`),
      #   KEY `ezcontentobject_attribute_co_id_ver_lang_code` (`contentobject_id`,`version`,`language_code`),
      # Column types:
      #	  `contentobject_id` int(11) not null default '0'
      #	  `version` int(11) not null default '0'
      #	  `language_code` varchar(20) not null default ''
      # To remove this duplicate index, execute:
      ALTER TABLE `ezpublish`.`ezcontentobject_attribute` DROP INDEX `ezcontentobject_attribute_contentobject_id`;
       
      # ########################################################################
      # ezpublish.ezenumobjectvalue                                             
      # ########################################################################
       
      # ezenumobjectvalue_co_attr_id_co_attr_ver is a left-prefix of PRIMARY
      # Key definitions:
      #   KEY `ezenumobjectvalue_co_attr_id_co_attr_ver` (`contentobject_attribute_id`,`contentobject_attribute_version`)
      #   PRIMARY KEY (`contentobject_attribute_id`,`contentobject_attribute_version`,`enumid`),
      # Column types:
      #	  `contentobject_attribute_id` int(11) not null default '0'
      #	  `contentobject_attribute_version` int(11) not null default '0'
      #	  `enumid` int(11) not null default '0'
      # To remove this duplicate index, execute:
      ALTER TABLE `ezpublish`.`ezenumobjectvalue` DROP INDEX `ezenumobjectvalue_co_attr_id_co_attr_ver`;
       
      # ########################################################################
      # ezpublish.ezkeyword                                                     
      # ########################################################################
       
      # ezkeyword_keyword is a left-prefix of ezkeyword_keyword_id
      # Key definitions:
      #   KEY `ezkeyword_keyword` (`keyword`),
      #   KEY `ezkeyword_keyword_id` (`keyword`,`id`)
      # Column types:
      #	  `keyword` varchar(255) default null
      #	  `id` int(11) not null auto_increment
      # To remove this duplicate index, execute:
      ALTER TABLE `ezpublish`.`ezkeyword` DROP INDEX `ezkeyword_keyword`;
       
      # Key ezkeyword_keyword_id ends with a prefix of the clustered index
      # Key definitions:
      #   KEY `ezkeyword_keyword_id` (`keyword`,`id`)
      #   PRIMARY KEY (`id`),
      # Column types:
      #	  `keyword` varchar(255) default null
      #	  `id` int(11) not null auto_increment
      # To shorten this duplicate clustered index, execute:
      ALTER TABLE `ezpublish`.`ezkeyword` DROP INDEX `ezkeyword_keyword_id`, ADD INDEX `ezkeyword_keyword_id` (`keyword`);
       
      # ########################################################################
      # ezpublish.ezkeyword_attribute_link                                      
      # ########################################################################
       
      # ezkeyword_attr_link_keyword_id is a left-prefix of ezkeyword_attr_link_kid_oaid
      # Key definitions:
      #   KEY `ezkeyword_attr_link_keyword_id` (`keyword_id`),
      #   KEY `ezkeyword_attr_link_kid_oaid` (`keyword_id`,`objectattribute_id`),
      # Column types:
      #	  `keyword_id` int(11) not null default '0'
      #	  `objectattribute_id` int(11) not null default '0'
      # To remove this duplicate index, execute:
      ALTER TABLE `ezpublish`.`ezkeyword_attribute_link` DROP INDEX `ezkeyword_attr_link_keyword_id`;
       
      # ########################################################################
      # ezpublish.eznode_assignment                                             
      # ########################################################################
       
      # eznode_assignment_co_id is a left-prefix of eznode_assignment_coid_cov
      # Key definitions:
      #   KEY `eznode_assignment_co_id` (`contentobject_id`),
      #   KEY `eznode_assignment_coid_cov` (`contentobject_id`,`contentobject_version`),
      # Column types:
      #	  `contentobject_id` int(11) default null
      #	  `contentobject_version` int(11) default null
      # To remove this duplicate index, execute:
      ALTER TABLE `ezpublish`.`eznode_assignment` DROP INDEX `eznode_assignment_co_id`;
       
      # ########################################################################
      # ezpublish.ezprest_clients                                               
      # ########################################################################
       
      # client_id is a left-prefix of client_id_unique
      # Key definitions:
      #   KEY `client_id` (`client_id`)
      #   UNIQUE KEY `client_id_unique` (`client_id`,`version`),
      # Column types:
      #	  `client_id` varchar(200) default null
      #	  `version` int(1) not null default '0'
      # To remove this duplicate index, execute:
      ALTER TABLE `ezpublish`.`ezprest_clients` DROP INDEX `client_id`;
       
      # ########################################################################
      # ezpublish.ezurlalias_ml                                                 
      # ########################################################################
       
      # ezurlalias_ml_actt is a left-prefix of ezurlalias_ml_actt_org_al
      # Key definitions:
      #   KEY `ezurlalias_ml_actt` (`action_type`),
      #   KEY `ezurlalias_ml_actt_org_al` (`action_type`,`is_original`,`is_alias`),
      # Column types:
      #	  `action_type` varchar(32) not null default ''
      #	  `is_original` int(11) not null default '0'
      #	  `is_alias` int(11) not null default '0'
      # To remove this duplicate index, execute:
      ALTER TABLE `ezpublish`.`ezurlalias_ml` DROP INDEX `ezurlalias_ml_actt`;
       
      # ########################################################################
      # Summary of indexes                                                      
      # ########################################################################
       
      # Size Duplicate Indexes   5831
      # Total Duplicate Indexes  8
      # Total Indexes            264
      

      #3 Upgrade MySQL/PostgreSQL/Oracle schema accordingly

      The biggest win, IMHO, should come from:
      ALTER TABLE `ezpublish`.`ezcontentobject_attribute` DROP INDEX `ezcontentobject_attribute_contentobject_id`;

        Issue Links

          Activity

          Hide
          Gaetano Giunta added a comment -

          Interesting!

          Just a note: in the linked blog post, it says that there is a small but non negligible difference between two indexes which are one a prefix of the other.

          So index removal should be done with care:
          . test for perf regressions
          . check for every index to be removed in which commit it was added and if it was tied to a specific issue

          Show
          Gaetano Giunta added a comment - Interesting! Just a note: in the linked blog post, it says that there is a small but non negligible difference between two indexes which are one a prefix of the other. So index removal should be done with care: . test for perf regressions . check for every index to be removed in which commit it was added and if it was tied to a specific issue
          Show
          Patrick Allaert (Inactive) added a comment - PR available at: https://github.com/ezsystems/ezpublish/pull/539 https://github.com/ezsystems/ezp-next/pull/195 https://github.com/ezsystems/ezoracle/pull/12
          Hide
          Paulo Nunes (Inactive) added a comment -

          No QA Needed

          Show
          Paulo Nunes (Inactive) added a comment - No QA Needed
          Hide
          Patrick Allaert (Inactive) added a comment -

          Oracle's PR was not accepted yet.

          Show
          Patrick Allaert (Inactive) added a comment - Oracle's PR was not accepted yet.

            People

            • Assignee:
              Unassigned
              Reporter:
              Patrick Allaert
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 1 day, 4 hours
                1d 4h
                Remaining:
                Time Spent - 1 day, 2 hours, 5 minutes Remaining Estimate - 30 minutes
                30m
                Logged:
                Time Spent - 1 day, 2 hours, 5 minutes Remaining Estimate - 30 minutes Time Not Required
                1d 2h 5m

                  Agile