FAQ: Planet Lotus upgrade / move.

[DarkRedSpiral]

Yancy no problem :-)

The reason you are getting the "Using temporary; Using filesort" from EXPLAIN
I believe is a combination of your GROUP BY and your our ORDER BY.
You do not (and cant) have an existing index on the calculated "HitCount" field so that
index has to be built. So this will be always forced into temporary by the File sort optimization engine
The benefit of the Sub Query is at the ORDER BY now only has to do 6 Items and not the entire
joined file.

You could try using a FORCE INDEX in the sub-query to ensure that the RSSID key is used by
the GROUP BY I don't think this will remove the "Using temporary; Using filesort" from EXPLAIN
but you may get a performance boost during the Group By data ordering as the SQL engine
may be forced to create a temporary index based on the the ORDER BY.

I would also try using a CAST on the COUNT(*) to force it to be a unsigned integer
as MySql returns the values as a UDT.. (i believe V4 of MYSQL returned a SIGNED)
a UDT can cause performance issues with sorting.

SELECT * FROM (
                         SELECT CAST(count(*) as UNSIGNED) as HitCount, hitsTABLE.rssid
                         FROM `hitsTABLE` FORCE INDEX (hitsTable.rssid)
                         WHERE   (hitsTABLE.created > (DATE_SUB( CURDATE( ) , INTERVAL 5 DAY ))
                         GROUP BY hitsTABLE.rssid
                         ORDER BY 1 DESC
                         Limit 6
                      ) AS TZero, 'rssTable' WHERE TZero.rssid = rssTable.id

Steve

[Yancy Lent]

Thanks again Steve!

It didn't like the FORCE INDEX (hitsTable.rssid) but it did everything else. Another huge enhancement was indexing the hitsTABLE.created column; expensive but huge performance increase on the select.

[clinfoot]

Just a quick question - what happened to the preview goggles?