CODEWITHSUNDEEP
mysqlperformanceoptimizationquery-optimization
HyderA
HyderA

Reputation: 21411

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

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?

Upvotes: 1

Views: 1748

Answers (2)

memnoch_proxy
memnoch_proxy

Reputation: 1954

Consider

alter table websites add index a (url, active);
select active 
    from websites 
    where url = 'google.com' 
    order by id desc 
    limit 1;

Upvotes: 0

SergeS
SergeS

Reputation: 11789

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

EDIT: Forgot where url

Upvotes: 1

Related Questions