Re: Sending data in processlist

[CD Ben]

Hi,

EXPLAIN EXTENDED result:
========================
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY test100 ref macIdx macIdx 9 const 1 100.00 Using where
2 SUBQUERY test100 ALL stateIdx,costIdx \N \N \N 21928 100.00 Using where

EXPLAIN EXTENDED result with FORCE INDEX:
=========================================
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY test100 ref macIdx macIdx 9 const 1 100.00 Using where
2 SUBQUERY test100 index_merge stateIdx,costIdx stateIdx,costIdx 5,9 \N 20101 100.00 Using sort_union(stateIdx,costIdx); Using where

SHOW CREATE TABLE result:
=========================
Table Create Table
test100 CREATE TABLE `test100` (
`pk_id` int(5) NOT NULL AUTO_INCREMENT,
`time_of_ping` datetime DEFAULT NULL,
`observedstate` int(2) DEFAULT '0',
`predictedstate` int(2) DEFAULT '0',
`controlledstate` int(2) DEFAULT '0',
`observedSmoothened` double DEFAULT NULL,
`weightedHistorical` double DEFAULT NULL,
`decision` double DEFAULT NULL,
`powerconsumption` double DEFAULT NULL,
`actualpowerconsumption` double DEFAULT NULL,
`simulatedpowerconsumption` double DEFAULT NULL,
`powercost` double DEFAULT NULL,
`actualpowercost` double DEFAULT NULL,
`powercostpredicted` double DEFAULT NULL,
`simulatedstate` int(2) DEFAULT '0',
`shutDownFlag` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`UserSatisfied` tinyint(1) DEFAULT '1',
`actualSavings` double DEFAULT NULL,
`cpuUser` double DEFAULT NULL,
`cpuSystem` double DEFAULT NULL,
`cpuIdle` double DEFAULT NULL,
`cpuTemperature` double DEFAULT '-1',
`suspendFailureReason` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
`overAllSavings` double DEFAULT NULL,
`isRecoveredData` tinyint(1) DEFAULT '0',
`isWakeupCommandIssued` tinyint(1) DEFAULT '0',
`accessIssue` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`pk_id`),
KEY `macIdx` (`time_of_ping`) USING BTREE,
KEY `stateIdx` (`observedstate`),
KEY `costIdx` (`powercost`)
) ENGINE=InnoDB AUTO_INCREMENT=20783 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I use FORCE INDEX in primary query/sub-query also like this but no luck.
SELECT shutDownFlag, time_of_ping, observedstate, simulatedstate FROM test100 FORCE INDEX (macIdx) WHERE time_of_ping = (SELECT MAX(time_of_ping) FROM test100 FORCE INDEX (stateIdx, costIdx) WHERE !(observedstate=4 AND powercost=0));

and my table also have index with time_of_ping(mcIdx), observedstate(stateIdx), powercost(costIdx)