Details
-
Improvement
-
Resolution: Fixed
-
High
-
4.6.0
-
None
-
Stetind Sprint 3, Stetind Sprint 4, Stetind Sprint 5
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`;
Attachments
Issue Links
- relates to
-
EZP-20194 Merge performance optimization branch from Qafoo
- Closed