Re: Super slow query

[Perrin Harkins]

On Wed, Jul 9, 2008 at 12:47 PM, Brian Dunning <[EMAIL PROTECTED]> wrote:
> Can someone spot a way to improve the performance of this query? I see that
> every time it runs, it's "Copying to tmp table" and then "Creating sort
> index" and taking way too long.

You're sorting by a computed field.  That's why it has to make the
temp table.  Either you can use a denormalized approach where you
pre-compute popcount, or you can just try to get it as fast as
possible before sorting and live with the sort hit.  If it's already
fast without the sort, I think you'll have to pre-compute.

- Perrin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Super slow query

[Brian Dunning]

Can someone spot a way to improve the performance of this query? I see  
that every time it runs, it's "Copying to tmp table" and then  
"Creating sort index" and taking way too long.


select count(distinct(stats.ip)) as popcount, stats.id, episodes.title  
from stats, episodes where stats.id=episodes.id and  
stats.type='listen' group by id order by popcount desc limit 12




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]