Results 1 to 1 of 1
  1. #1
    Join Date
    Jan 2009
    Location
    NYC
    Posts
    11

    Unanswered: Update using OR InnoDB slow

    I have a table:
    CREATE TABLE `ezcontentobject_tree` (
    `contentobject_id` int(11) default NULL,
    `contentobject_is_published` int(11) default NULL,
    `contentobject_version` int(11) default NULL,
    `depth` int(11) NOT NULL default '0',
    `is_hidden` int(11) NOT NULL default '0',
    `is_invisible` int(11) NOT NULL default '0',
    `main_node_id` int(11) default NULL,
    `modified_subnode` int(11) default '0',
    `node_id` int(11) NOT NULL auto_increment,
    `parent_node_id` int(11) NOT NULL default '0',
    `path_identification_string` longtext,
    `path_string` varchar(255) NOT NULL default '',
    `priority` int(11) NOT NULL default '0',
    `remote_id` varchar(100) NOT NULL default '',
    `sort_field` int(11) default '1',
    `sort_order` int(11) default '1',
    PRIMARY KEY (`node_id`),
    KEY `ezcontentobject_tree_co_id` (`contentobject_id`),
    KEY `ezcontentobject_tree_depth` (`depth`),
    KEY `ezcontentobject_tree_p_node_id` (`parent_node_id`),
    KEY `ezcontentobject_tree_path` (`path_string`),
    KEY `ezcontentobject_tree_path_ident` (`path_identification_string`(50)),
    KEY `modified_subnode` (`modified_subnode`)
    ) ENGINE=InnoDB AUTO_INCREMENT=281680 DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 3409920 kB; InnoDB free: 3402752 kB';

    The table has about 230,000 rows.
    When i execute two or more update statements at the same time like this:
    UPDATE ezcontentobject_tree SET modified_subnode=1244129530 WHERE node_id='1' OR node_id='2' OR node_id='63' OR node_id='232' OR node_id='633' OR node_id='2610' OR node_id='273790';
    UPDATE ezcontentobject_tree SET modified_subnode=1244147962 WHERE node_id='1' OR node_id='2' OR node_id='64' OR node_id='267298' OR node_id='267374' OR node_id='268799281534';
    The database response time varies from 10 seconds to 1 minute.
    When executed separately they finish under 1 second.

    Unfortunately i cannot modify these queries.
    Is there anything that can be done?

    my.cnf attached.


    Thank you
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •