问题描述
我在运行此SQL时遇到麻烦:
I'm having troubles to run this SQL:
我认为它是一个index problem
,但我不知道,因为我没有建立该数据库,而且我只是一个简单的程序员.
I think it's a index problem
but I don't know because I dind't make this database and I'm just a simple programmer.
问题在于,该表具有64260条记录,因此查询在执行时变得疯狂,我必须停止mysql并再次运行,因为计算机被冻结了.
The problem is, that table has 64260 records, so that query gets crazy when executing, I have to stop mysql and run again because the computer get frozen.
谢谢.
表架构
CREATE TABLE IF NOT EXISTS `value_magnitudes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` float DEFAULT NULL,
`magnitude_id` int(11) DEFAULT NULL,
`sdi_belongs_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`reading_date` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1118402 ;
查询
select * from value_magnitudes
where id in
(
SELECT min(id)
FROM value_magnitudes
WHERE magnitude_id = 234
and date(reading_date) >= '2013-04-01'
group by date(reading_date)
)
EDIT2
推荐答案
首先,在(magnitude_id, reading_date)
上添加索引:
ALTER TABLE
ADD INDEX magnitude_id__reading_date__IX -- just a name for the index
(magnitude_id, reading_date) ;
然后尝试以下变体形式:
Then try this variation:
SELECT vm.*
FROM value_magnitudes AS vm
JOIN
( SELECT MIN(id) AS id
FROM value_magnitudes
WHERE magnitude_id = 234
AND reading_date >= '2013-04-01' -- changed so index is used
GROUP BY DATE(reading_date)
) AS vi
ON vi.id = vm.id ;
GROUP BY DATE(reading_date)
仍然需要将函数应用于所有选定的行(通过索引),除非您遵循@jurgen的建议并将该列拆分为date
和time
列,否则无法对其进行改进.
The GROUP BY DATE(reading_date)
will still need to apply the function to all the selected (thorugh the index) rows and that cannot be improved, unless you follow @jurgen's advice and split the column into date
and time
columns.
这篇关于MySQL innoDB:查询执行时间长的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!