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/[EMAIL PROTECTED]
--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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
