performance: mysql + add. index

Saturday 06 March 2004 10:41:22 am

Hi,

I've tried to analyze (explain select ...) the queries which were made by ez. I've added several indexes which speeds up the system.

(use at your own risk ;-)

Here they are:

ALTER TABLE `ezcontentclass_attribute` ADD INDEX `ver_idx` ( `version` );
ALTER TABLE `ezcontentobject_attribute` ADD INDEX `attributeid_dataint_idx` ( `contentclassattribute_id` , `data_int` );
ALTER TABLE `ezcontentobject_attribute` ADD INDEX ( `data_type_string` ( 50 ) );
ALTER TABLE `ezcontentobject_attribute` ADD INDEX `ccai_idx` ( `contentclassattribute_id` ) ;
ALTER TABLE `ezcontentobject_attribute` ADD INDEX `cai_ver_idx` ( `contentclassattribute_id` , `version` );

ALTER TABLE `ezcontentobject_name` ADD INDEX `tran_ver_idx` ( `content_translation` , `content_version` );
ALTER TABLE `ezcontentobject_name` ADD INDEX `real_idx` ( `real_translation` ( 6 ) );
ALTER TABLE `ezcontentobject_name` ADD INDEX `trans_vers_id_idx` ( `content_translation` ( 6 ), `content_version`, `contentobject_id` );
ALTER TABLE `ezcontentobject_link` ADD INDEX `from_id_ver_idx` ( `from_contentobject_id` , `from_contentobject_version` );
ALTER TABLE `ezcontentobject_tree` ADD INDEX `ver_coid_idx` ( `contentobject_version` , `contentobject_id` );
ALTER TABLE `ezcontentobject_tree` ADD INDEX `depth_path_idx` ( `depth` , `path_string` ( 50 ) ) ;
ALTER TABLE `ezcontentobject_tree` ADD INDEX `mnodeid_idx` ( `main_node_id` );
ALTER TABLE `ezcontentobject_version` ADD INDEX `ver_idx` ( `version` );
ALTER TABLE `ezcontentobject_version` ADD INDEX `ver_co_idx` ( `version` , `contentobject_id` );
ALTER TABLE `ezcontentobject_version` ADD INDEX `stat_co_idx` ( `status` , `contentobject_id` ) ;
ALTER TABLE `ezsearch_return_count` ADD INDEX `phrase_idx` ( `phrase_id` ) ;
ALTER TABLE `ezpolicy` ADD INDEX `role_id_idx` ( `role_id` );
ALTER TABLE `ezpolicy_limitation` ADD INDEX `policy_id_idx` ( `policy_id` );
ALTER TABLE `ezpolicy_limitation` ADD INDEX `identifier_idx` ( `identifier` ( 50 ) );
ALTER TABLE `ezpolicy_limitation` ADD INDEX `identifier_po_idx` ( `identifier` ( 50 ) , `policy_id` );
ALTER TABLE `ezpolicy_limitation_value` ADD INDEX `limit_idx` ( `limitation_id` ) ;
ALTER TABLE `ezuser_discountrule` ADD INDEX `discount_id_idx` ( `discountrule_id` );
ALTER TABLE `ezuser_discountrule` ADD INDEX `co_idx` ( `contentobject_id` ) ;
ALTER TABLE `ezuser_discountrule` ADD INDEX `di_co_idx` ( `discountrule_id` , `contentobject_id` );
ALTER TABLE `ezinfocollection` ADD INDEX `coid_idx` ( `contentobject_id` );
ALTER TABLE `ezinfocollection` ADD INDEX `uid_idx` ( `user_identifier` );
ALTER TABLE `ezkeyword` ADD INDEX `class_id_keyword_idx` ( `class_id` , `keyword` );
ALTER TABLE `ezkeyword` ADD INDEX `class_idx` ( `class_id` );
ALTER TABLE `ezkeyword` ADD INDEX `keyw_idx` ( `keyword` );
ALTER TABLE `ezkeyword_attribute_link` ADD INDEX `kid_idx` ( `keyword_id` )
ALTER TABLE `ezkeyword_attribute_link` ADD INDEX `oid_idx` ( `objectattribute_id` );
ALTER TABLE `ezkeyword_attribute_link` ADD INDEX `oid_kid_idx` ( `objectattribute_id` , `keyword_id` );
ALTER TABLE `eznode_assignment` ADD INDEX `pid_idx` ( `parent_node` ) ;
ALTER TABLE `eznode_assignment` ADD INDEX `sort_idx` ( `sort_order` );
ALTER TABLE `eznode_assignment` ADD INDEX `ver_oid_idx` ( `contentobject_version` , `contentobject_id` );
ALTER TABLE `ezorder` ADD INDEX `uid_idx` ( `user_id` );
ALTER TABLE `ezurlalias` ADD INDEX `wildcard_idx` ( `is_wildcard` ) ;

Kind regards,
Emil.

Best wishes,
Georg.

--
http://www.schicksal.com Horoskop website which uses eZ Publish since 2004

Sunday 07 March 2004 7:20:04 am

Hi Paul,

the very strange thing: Inserting or updating content is a bit faster too (1 up to 2 seconds). But I don't have tested that a lot. My db has 300 MB, the largest tables are ezcontentobject_atribute (around 300.000 entries) and ezsearch_object_word_link (1.7 mio. entries). I am using mysql 4.1 and have turned the query cache on.

In the other case (if inserting is slowing down the system) I would also accept more indexes because over 90% of the queries are select queries.

Kind regards,
Emil.

Best wishes,
Georg.

--
http://www.schicksal.com Horoskop website which uses eZ Publish since 2004