i have a table with about 5kk records
CREATE TABLE IF NOT EXISTS `termo` (
`id` int(11) NOT NULL auto_increment,
`date` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
`sensor` varchar(16) NOT NULL,
`temp` float NOT NULL default '-255'
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=5466795 ;
indexes
ALTER TABLE `termo` ADD PRIMARY KEY (`id`), ADD KEY `date_3` (`date`,`sensor`);
query
select pressure, humidity, temp_a, voltage, aqua_temp, home_temp, home_hum, current from
(select temp as pressure from termo force index(date_3) where sensor='000461965E3901' order by date desc limit 0,1) as pressure,
(select temp as humidity from termo force index(date_3) where sensor='000461965E3903' order by date desc limit 0,1) as humidity,
(select temp as temp_a from termo force index(date_3) where sensor='000461965E3902' order by date desc limit 0,1) as temp_a,
(select temp as voltage from termo force index(date_3) where sensor='000461965E3904' order by date desc limit 0,1) as voltage,
(select temp as aqua_temp from termo force index(date_3) where sensor='000461965E3907' order by date desc limit 0,1) as aqua_temp,
(select temp as home_temp from termo force index(date_3) where sensor='000461965E3905' order by date desc limit 0,1) as home_temp,
(select temp as current from termo force index(date_3) where sensor='000461965E3911' order by date desc limit 0,1) as current,
(select temp as home_hum from termo force index(date_3) where sensor='000461965E3906' order by date desc limit 0,1) as home_hum
works very slow... about 30 seconds. please help optimize.
explain looks like
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY <derived3> system NULL NULL NULL NULL 1
1 PRIMARY <derived4> system NULL NULL NULL NULL 1
1 PRIMARY <derived5> system NULL NULL NULL NULL 1
1 PRIMARY <derived6> system NULL NULL NULL NULL 1
1 PRIMARY <derived7> system NULL NULL NULL NULL 1
1 PRIMARY <derived8> system NULL NULL NULL NULL 1
1 PRIMARY <derived9> system NULL NULL NULL NULL 1
9 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
8 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
7 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
6 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
5 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
4 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
3 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
2 DERIVED termo index NULL date_3 3232 NULL 5193950 Using where
I think this can be a better way, but need add INDEX for only sensor so IN statement work faster.
SELECT termo.`sensor`, `temp`
FROM termo
JOIN (
SELECT `sensor`, MAX(`date`) as `lastTemp`
FROM termo
WHERE sensor IN ('000461965E3901', '000461965E3903', '000461965E3902',
'000461965E3904', '000461965E3907', '000461965E3905',
'000461965E3911', '000461965E3906')
GROUP BY sensor
) T
ON termo.`date` = T.`lastTemp`
AND termo.`sensor` = T.`sensor`
Also consider test with a composite INDEX (temp, date, sensor)
To get the right label use
SELECT
MAX(CASE WHEN termo.sensor = '000461965E3901' THEN temp END) as pressure,
MAX(CASE WHEN termo.sensor = '000461965E3903' THEN temp END) as humidity ,
MAX(CASE WHEN termo.sensor = '000461965E3902' THEN temp END) as temp_a,
MAX(CASE WHEN termo.sensor = '000461965E3904' THEN temp END) as voltage ,
MAX(CASE WHEN termo.sensor = '000461965E3907' THEN temp END) as aqua_temp ,
MAX(CASE WHEN termo.sensor = '000461965E3905' THEN temp END) as home_temp ,
MAX(CASE WHEN termo.sensor = '000461965E3911' THEN temp END) as current ,
MAX(CASE WHEN termo.sensor = '000461965E3906' THEN temp END) as home_hum
FROM termo
JOIN (
SELECT `sensor`, MAX(`date`) as `lastTemp`
FROM termo
WHERE sensor IN ('000461965E3901', '000461965E3903', '000461965E3902',
'000461965E3904', '000461965E3907', '000461965E3905',
'000461965E3911', '000461965E3906')
GROUP BY sensor
) T
ON termo.`date` = T.`lastTemp`
AND termo.`sensor` = T.`sensor`
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments