Arch Linux

print

Member

Registered: 2007-02-27

Posts: 174

MySQL: Help with correlated subquery

SELECT uid,header, click,ip,SUM(seconds),COUNT(*),
      (SELECT SUM(seconds)
        FROM my.site
        WHERE (uid,header,click,ip) =
          (w.uid,w.header,w.click,w.ip)) AS mseconds,
      (SELECT COUNT(*)
        FROM my.site
        WHERE (uid,header,click,ip) =
          (w.uid,w.header,w.click,w.ip)) AS mrows
FROM my.site AS w
WHERE (header, click, ip) = (oheader,oclick,oip)
GROUP BY uid,header,oheader,oclick,ip,oip
ORDER BY ip DESC

It's slow.  Please help to optimize.  I think I need a join but don't know how?

---

% whereis whatis whence which whoami whois who

Shaika-Dzari

Member

From: Québec, Canada

Registered: 2006-04-14

Posts: 436

Website

Re: MySQL: Help with correlated subquery

Can you explain what exactly you want to do?
I dont really understand why you did Count(*) and sum twice...

@+

---

Shaika-Dzari
http://www.4nakama.net

High|ander

Member

From: Skövde, Sweden

Registered: 2005-10-28

Posts: 188

Website

Re: MySQL: Help with correlated subquery

The general thing to forget is not to have index on the columns that you search on.

---

When death smiles at you, all you can do is smile back!
Blog

print

Member

Registered: 2007-02-27

Posts: 174

Re: MySQL: Help with correlated subquery

The count(*) is done because the there are two different WHERE clauses, so the number of results returned for each would be different.

I added indexes and it's a hell of a lot faster.

Thanks

print

---

% whereis whatis whence which whoami whois who