Description:
SELECT SQL_CALC_FOUND_ROWS id, ref_id, status, title, UNIX_TIMESTAMP(updated), has_draft, locked_by, UNIX_TIMESTAMP(lock_time), UNIX_TIMESTAMP(created), author_id, updated_by, section_id, valid_from_time, valid_to_time, hash_key, UNIX_TIMESTAMP(published), published_by FROM phaistos_cman.items WHERE section_id IN (166) AND status IN (1,2) AND id IN (SELECT item_id FROM phaistos_cman.item_data WHERE section_id IN (166) AND ( (field_id = 281 AND data REGEXP '^a') ) GROUP BY item_id HAVING COUNT(*) = 1) ORDER BY id LIMIT 0, 10
The above query is using the IN operator in conjuction with a result-set returned by a subselect:
SELECT item_id FROM phaistos_cman.item_data WHERE section_id IN (166) AND ( (field_id = 281 AND data REGEXP '^a') ) GROUP BY item_id HAVING COUNT(*) = 1
This query takes almost 40 seconds. If we remove the 'GROUP BY item_id HAVING COUNT(*) = 1' part from the subquery it takes less than a second.
Invoking:
SELECT item_id FROM phaistos_cman.item_data WHERE section_id IN (166) AND ( (field_id = 281 AND data REGEXP '^a') ) GROUP BY item_id HAVING COUNT(*) = 1
on its own (i.e not as a sub-query) takes less than a second.
Invoking:
SELECT item_id FROM phaistos_cman.item_data WHERE section_id IN (166) AND ( (field_id = 281 AND data REGEXP '^a') )
on its own, also takes less than a second.
It seems that when using GROUP BY in a sub-select, something is slowing down the execution too much (1 sec VS 40 seconds).
Upgrading from 4.1 to 4.12 did not fix the problem.
How to repeat:
Here is the definition of the two tables.
If you populate them with random data, you will be able to reproduce the problem.
CREATE TABLE `items` (
`id` int(11) NOT NULL default '0',
`ref_id` varchar(255) NOT NULL default '',
`flags` int(10) unsigned NOT NULL default '0',
`status` tinyint(1) NOT NULL default '0',
`title` varchar(255) NOT NULL default '',
`updated` datetime NOT NULL default '0000-00-00 00:00:00',
`draft_title` varchar(255) NOT NULL default '',
`draft_ref_id` varchar(255) NOT NULL default '',
`updated_by` int(11) NOT NULL default '0',
`workflow_pos` int(2) NOT NULL default '0',
`has_draft` tinyint(1) NOT NULL default '0',
`hash_key` varchar(127) NOT NULL default '',
`disapprove_reason` text NOT NULL,
`section_id` int(11) NOT NULL default '0',
`locked_by` int(11) NOT NULL default '0',
`lock_time` datetime NOT NULL default '0000-00-00 00:00:00',
`draft_updated` datetime NOT NULL default '0000-00-00 00:00:00',
`valid_from_time` int(11) NOT NULL default '0',
`valid_to_time` int(11) NOT NULL default '0',
`keywords` text NOT NULL,
`descr` text NOT NULL,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`pos` int(11) NOT NULL default '0',
`series_id` int(11) NOT NULL default '0',
`allow_comments` int(11) NOT NULL default '0',
`draft_author_id` int(11) NOT NULL default '0',
`author_id` int(10) unsigned NOT NULL default '0',
`published` datetime default '0000-00-00 00:00:00',
`published_by` int(10) unsigned default '0',
PRIMARY KEY (`section_id`,`id`),
KEY `ref_id` (`ref_id`),
KEY `section_id` (`status`),
KEY `status` (`status`,`id`),
KEY `section_id_2` (`section_id`),
KEY `section_id_4` (`section_id`,`status`,`valid_from_time`,`valid_to_time`),
KEY `section_id_5` (`section_id`,`created`),
KEY `series_id` (`series_id`,`pos`),
KEY `series_id_2` (`series_id`,`status`),
KEY `section_id_3` (`section_id`,`hash_key`),
KEY `authorKey` (`author_id`,`status`,`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=greek
CREATE TABLE `item_data` (
`item_id` int(11) NOT NULL default '0',
`field_id` int(11) NOT NULL default '0',
`group_field_id` int(11) NOT NULL default '0',
`data` blob NOT NULL,
`id` int(11) NOT NULL default '0',
`section_id` int(11) NOT NULL default '0',
`lang` tinyint(2) NOT NULL default '0',
PRIMARY KEY (`section_id`,`item_id`,`id`,`lang`),
KEY `item_field` (`item_id`,`field_id`,`group_field_id`),
KEY `item_id` (`item_id`,`id`),
KEY `item_id_2` (`item_id`,`field_id`,`section_id`)
) ENGINE=InnoDB DEFAULT CHARSET=greek
