2 个相同的 sql 查询diff params - 只有一个需要临时表

发布于 2024-09-26 20:33:32 字数 1502 浏览 5 评论 0原文

这些 sql 查询的唯一区别是 record_id 参数(这是我分页整个结果集的方式)。桌子是myisam。第一个查询执行得很好,而第二个查询则非常慢。知道为什么会这样吗?

这个查询工作正常

explain select r.record_id, r.oai_datestamp, r.format_id, r.status,  x.xml,  max(u.date_updated) as date_updated 
from  marcnormalization.records r, 
   marcnormalization.records_xml x, 
   marcnormalization.record_updates u 
where r.record_id = x.record_id 
  and (r.record_id > 1802000 or 1802000 is null) 
  and r.record_id = u.record_id 
  and (u.date_updated > '1960-10-19 10:18:52.0' or '1960-10-19 10:18:52.0' is null) 
  and u.date_updated <= '2010-10-07 10:18:52.0'
group by u.record_id 
order by u.record_id 
limit 1000;

这个查询非常慢(创建一个临时表)

explain select r.record_id, r.oai_datestamp, r.format_id, r.status,  x.xml,  max(u.date_updated) as date_updated 
from  marcnormalization.records r, 
   marcnormalization.records_xml x, 
   marcnormalization.record_updates u 
where r.record_id = x.record_id 
  and (r.record_id > 2202000 or 2202000 is null) 
  and r.record_id = u.record_id 
  and (u.date_updated > '1960-10-19 10:18:52.0' or '1960-10-19 10:18:52.0' is null) 
  and u.date_updated <= '2010-10-07 10:18:52.0'
group by u.record_id 
order by u.record_id 
limit 1000;

更新:我已经通过从更改为解决了我的问题

group by u.record_id 
order by u.record_id 

所以

group by r.record_id 
order by r.record_id 

,现在这是一个有争议的问题,但我仍然对最初的问题感到好奇。

The only difference in these sql queries is the record_id param (it's how I page through entire result set). The tables are myisam. The first query performs well and the second is terribly slow. Any idea why this would be?

This query works fine

explain select r.record_id, r.oai_datestamp, r.format_id, r.status,  x.xml,  max(u.date_updated) as date_updated 
from  marcnormalization.records r, 
   marcnormalization.records_xml x, 
   marcnormalization.record_updates u 
where r.record_id = x.record_id 
  and (r.record_id > 1802000 or 1802000 is null) 
  and r.record_id = u.record_id 
  and (u.date_updated > '1960-10-19 10:18:52.0' or '1960-10-19 10:18:52.0' is null) 
  and u.date_updated <= '2010-10-07 10:18:52.0'
group by u.record_id 
order by u.record_id 
limit 1000;

this query is super slow (creates a temporary table)

explain select r.record_id, r.oai_datestamp, r.format_id, r.status,  x.xml,  max(u.date_updated) as date_updated 
from  marcnormalization.records r, 
   marcnormalization.records_xml x, 
   marcnormalization.record_updates u 
where r.record_id = x.record_id 
  and (r.record_id > 2202000 or 2202000 is null) 
  and r.record_id = u.record_id 
  and (u.date_updated > '1960-10-19 10:18:52.0' or '1960-10-19 10:18:52.0' is null) 
  and u.date_updated <= '2010-10-07 10:18:52.0'
group by u.record_id 
order by u.record_id 
limit 1000;

update: I've gotten through my issue by changing from

group by u.record_id 
order by u.record_id 

to

group by r.record_id 
order by r.record_id 

So, it's kind of a moot point now, but I'm still curious as to the initial question.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

九八野马 2024-10-03 20:33:32

我认为这种情况也与您的连接排序区域有关。
您可以增加此会话范围。
尝试这样;

mysql>选择@@max_heap_table_size;

mysql>设置会话 max_heap_table_size=19777216;

之后执行查询。

巴里斯·阿克维尔迪

i think that this case related with your connection sort area also.
You can increase this are for session.
try like this;

mysql> select @@max_heap_table_size;

mysql> SET SESSION max_heap_table_size=19777216;

after that execute the query.

Baris Akverdi

水溶 2024-10-03 20:33:32

怎么样:

EXPLAIN
SELECT r.record_id, r.oai_datestamp, r.format_id, r.status,  x.xml,  max(u.date_updated) as date_updated 
FROM marcnormalization.records_xml x
INNER JOIN (SELECT * FROM marcnormalization.records WHERE record_id > 1802000) r
ON r.record_id = x.record_id
INNER JOIN (SELECT * FROM marcnormalization.record_updates WHERE date_updated BETWEEN '1960-10-19 10:18:52.0' AND '2010-10-07 10:18:52.0') u
ON r.record_id = u.record_id
group by u.record_id ASC
limit 1000;

我认为它可以更快?

What about :

EXPLAIN
SELECT r.record_id, r.oai_datestamp, r.format_id, r.status,  x.xml,  max(u.date_updated) as date_updated 
FROM marcnormalization.records_xml x
INNER JOIN (SELECT * FROM marcnormalization.records WHERE record_id > 1802000) r
ON r.record_id = x.record_id
INNER JOIN (SELECT * FROM marcnormalization.record_updates WHERE date_updated BETWEEN '1960-10-19 10:18:52.0' AND '2010-10-07 10:18:52.0') u
ON r.record_id = u.record_id
group by u.record_id ASC
limit 1000;

I think it can be faster ?

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文