查询优化:子查询中的 max() [英] Query optimization: max() in subquery

问题描述

select active from websites where id = (select max(id) from websites where url = 'google.com')

id  select_type table     type   possible_keys  key      key_len  ref    rows  Extra
1   PRIMARY     websites  const  PRIMARY        PRIMARY  4        const  1   
2   SUBQUERY    websites  ref    url            url      767             1867  Using where

如何优化此查询?url 字段是索引，id 是主键.那么为什么它会遍历所有行?

How can I optimize this query? The url field is index and id is the primary key. So why is it going through all the rows?

推荐答案

MAX 总是处理所有行 - 使用 order by 和 limit - 所以查询看起来会这样

MAX always process all rows - use order by and limit - so query will look this way

 SELECT * FROM wbsites WHERE url = '...' ORDER BY id DESC LIMIT 1

对于这种情况，不需要子查询

For this case subquery is not required

忘记网址

这篇关于查询优化:子查询中的 max()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！