Insert into select查询耗时超过10分钟
Insert into select查询耗时超过10分钟
提问于 2012-09-06 04:02:58
当我在几周前运行一个查询时，它只用了不到1分钟，现在却花了10多分钟，而且看不到结束的迹象。
新查询(耗时较长)
select sds.school_id,
detail.year,
detail.race,
ROUND((detail.count / summary.total) * 100 ,2) as percent
FROM school_data_race_ethnicity_raw as detail
inner join school_data_schools as sds USING (school_id)
inner join (
select sds2.district_id, year, sum(count) as total
from school_data_race_ethnicity_raw
inner join school_data_schools as sds2 USING (school_id)
group by sds2.district_id, year
) as summary on summary.district_id = sds.district_id
and summary.year = detail.year查询：
INSERT INTO school_data_race_ethnicity_schools (school_id, year, race, percent) (
SELECT school_id,
year,
race,
ROUND((count/(
SELECT SUM(count)
FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner
WHERE school_id = school_data_race_ethnicity_raw_outer.school_id
and year = school_data_race_ethnicity_raw_outer.year)
) * 100,2) as percent
FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_outer) 解释：
mysql> explain SELECT school_id,year,race,ROUND((count/(SELECT SUM(count)
-> FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner
-> WHERE
-> school_id = school_data_race_ethnicity_raw_outer.school_id and
-> year = school_data_race_ethnicity_raw_outer.year)) * 100,2) as percent
-> FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_outer;
+----+--------------------+--------------------------------------+------+----------------------------+------+---------+-----------------------------------------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+--------------------------------------+------+----------------------------+------+---------+-----------------------------------------------------------------+-------+-------------+
| 1 | PRIMARY | school_data_race_ethnicity_raw_outer | ALL | NULL | NULL | NULL | NULL | 84012 | |
| 2 | DEPENDENT SUBQUERY | school_data_race_ethnicity_raw_inner | ref | school_id,year,school_id_2 | year | 4 | rocdocs_main_drupal_7.school_data_race_ethnicity_raw_outer.year | 8402 | Using where |
+----+--------------------+--------------------------------------+------+----------------------------+------+---------+-----------------------------------------------------------------+-------+-------------+
2 rows in set (0.00 sec)创建表：
mysql> show create table school_data_race_ethnicity_raw;
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| school_data_race_ethnicity_raw | CREATE TABLE `school_data_race_ethnicity_raw` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`school_id` varchar(255) NOT NULL,
`year` int(11) NOT NULL,
`race` varchar(255) NOT NULL,
`count` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `school_id` (`school_id`,`year`),
KEY `year` (`year`,`race`),
KEY `school_id_2` (`school_id`)
) ENGINE=MyISAM AUTO_INCREMENT=84013 DEFAULT CHARSET=latin1 |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table school_data_race_ethnicity_schools;
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| school_data_race_ethnicity_schools | CREATE TABLE `school_data_race_ethnicity_schools` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`school_id` varchar(255) NOT NULL,
`year` int(11) NOT NULL,
`race` varchar(255) NOT NULL,
`percent` decimal(15,2) NOT NULL,
PRIMARY KEY (`id`),
KEY `year` (`year`,`race`),
KEY `school_id` (`school_id`,`year`)
) ENGINE=MyISAM AUTO_INCREMENT=24961 DEFAULT CHARSET=latin1 |
+------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+------+---------+--------------------+-----------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+---------+--------------------+-----------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 1739 | [REMOVED] | [REMOVED] | rocdocs_main_drupal_7 | Query | 1467 | Sending data | INSERT INTO school_data_race_ethnicity_schools (school_id, year, race, percent) (
SELECT school_id,y |
| 1800 | root | localhost | rocdocs_main_drupal_7 | Query | 0 | NULL | show processlist |
+------+---------+--------------------+-----------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)回答 2
Stack Overflow用户
回答已采纳
发布于 2012-09-06 04:16:56
您的SELECT将非常慢，因为您使用子查询来计算百分比。它读取每行一整年的数据。如果使用子查询来选择总计并连接到该子查询，那么它的运行速度应该会快得多。
在我的脑海中，像这样的东西(虽然不理想)应该比你现有的查询快得多：
select detail.school_id,
detail.year,
detail.race,
ROUND((detail.count / summary.total) * 100 ,2) as percent
FROM school_data_race_ethnicity_raw as detail
inner join (
select school_id, year, sum(count) as total
from school_data_race_ethnicity_raw
group by school_id, year
) as summary on summary.school_id = detail.school_id
and summary.year = detail.yearStack Overflow用户
发布于 2012-09-06 04:34:28
让我们研究一下SELECT查询。让我们一块一块地来做这个。看起来您需要为每个不同的学校、种族和年份占一行。这很简单。
SELECT r.school_id, r.year, r.race, something
FROM school_data_race_ethnicity_raw r
GROUP BY r.school_id, r.year, r.race现在，让我们整理一下您的something指标。很难从你的问题中准确地判断出来，但我会猜测。看起来你想要属于每个种族的学生的分数。
因此，我们需要处理两个摘要查询。其中一个给出了每所学校每年的学生总数。
SELECT r.school_id, r.year, SUM(count) count
FROM school_data_race_ethnicity_raw r
GROUP BY r.school_id, r.year第二个给出了按学校、年份和种族划分的学生总数。
SELECT r.school_id, r.year, r.race, SUM(count) count
FROM school_data_race_ethnicity_raw r
GROUP BY r.school_id, r.year, r.race然后，我们需要将这两个查询连接在一起，就像它们是虚拟表一样，这样我们就可以做分数了：
SELECT t.school_id, t.year, u.race, u.count/t.count percent
FROM
(
SELECT r.school_id, r.year, SUM(count) count
FROM school_data_race_ethnicity_raw r
GROUP BY r.school_id, r.year
) t
LEFT JOIN
(
SELECT r.school_id, r.year, r.race, SUM(count) count
FROM school_data_race_ethnicity_raw r
GROUP BY r.school_id, r.year, r.race
) u ON (t.school_id = u.school_id AND t.year = u.year)最后，您需要百分比，所以让我们将分数乘以100.0。
SELECT t.school_id, t.year, u.race, ROUND(100.0*u.count/t.count, 2) percent
FROM
(
SELECT r.school_id, r.year, SUM(count) count
FROM school_data_race_ethnicity_raw r
GROUP BY r.school_id, r.year
) t
LEFT JOIN
(
SELECT r.school_id, r.year, r.race, SUM(count) count
FROM school_data_race_ethnicity_raw r
GROUP BY r.school_id, r.year, r.race
) u ON (t.school_id = u.school_id AND t.year = u.year)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12288909
复制相关文章
相似问题
腾讯云开发者
