From: Peter Brawley
Date: August 13 2006 4:29pm
Subject: Re: Do I really need a subquery?
List-Archive: http://lists.mysql.com/mysql/200904
Message-Id: <44DF5363.9010009@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Geoffrey,

 >Do I really need that subquery?

You need its scalar result just once, so why not ...

SELECT url, @maxcount:=COUNT(1)
FROM bi_bookmarks
WHERE date > NOW() - INTERVAL 86400 SECOND
GROUP BY url
ORDER BY count DESC LIMIT 0, 1;

SELECT
  title,
  url,
  COUNT(1) AS  count,
  CEIL(COUNT(1) / (@maxcount * 8) AS weight
FROM bi_bookmarks
WHERE date > NOW() - INTERVAL 86400 SECOND
GROUP BY url
ORDER BY  count DESC LIMIT 0, 10;

PB

-----

Geoffrey Sneddon wrote:
> Hi,
>
> I've ended up with the following SQL:
>
> SELECT `title`, `url`, COUNT(1) AS  `count`, CEIL(COUNT(1) / (SELECT 
> COUNT(1) AS `count` FROM `bi_bookmarks` WHERE `date` > NOW() - 
> INTERVAL 86400 SECOND GROUP BY `url` ORDER BY `count` DESC LIMIT 0, 1) 
> * 8) AS `weight` FROM `bi_bookmarks` WHERE `date` > NOW() - INTERVAL 
> 86400 SECOND GROUP BY `url` ORDER BY  `count` DESC LIMIT 0, 10;
>
> Do I really need that subquery? It seems rather pointless having 
> similar queries like that. Any other optimisations tips are of course 
> welcome (just to note, there are reasons for having the interval in 
> seconds).
>
> - Geoffrey Sneddon
>
>
>
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=peter.brawley@stripped
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.405 / Virus Database: 268.10.9/417 - Release Date: 8/11/2006
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.10.9/417 - Release Date: 8/11/2006