MySQL innoDB:查询执行时间长 [英] MySQL innoDB: Long time of query execution

问题描述

我在运行此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:查询执行时间长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！