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=1
>
>
>
> --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
