[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Optimizing GROUP BY and ORDER BY
From:       "Michael Stearne" <mstearne () entermix ! com>
Date:       2008-07-25 16:39:45
Message-ID: 293e03f00807250939t47dd28bbjcfcd19ca8f255cd1 () mail ! gmail ! com
[Download RAW message or body]

On Fri, Jul 25, 2008 at 12:35 PM, Arthur Fuller <fuller.artful@gmail.com> wrote:
> ORDER BY implies a sort of the result set. I don't think there is any way
> around that.

I guess so. What I am doing is to just run the query once per day and
store the results in memcache.

Michael


> 
> Arthur
> 
> On Fri, Jul 25, 2008 at 4:27 AM, Michael Stearne <mstearne@entermix.com>
> wrote:
> > 
> > I have a query:
> > 
> > SELECT Country, COUNT( Country ) AS Cnt FROM properties WHERE (
> > Country != 'USA' AND Country != 'US' AND Country != 'Unit' AND Country
> > != 'United States' AND Country != ' ' AND Country IS NOT NULL ) GROUP
> > BY Country ORDER BY Cnt DESC LIMIT 8
> > 
> > that gets the top 8 non-US countries from the properties table. There
> > is about 500,000 rows in the properties table.
> > 
> > This is a costly query:
> > 
> > +----+-------------+------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
> >  | id | select_type | table      | type  | possible_keys | key     |
> > key_len | ref  | rows  | Extra
> > > 
> > 
> > +----+-------------+------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
> >  |  1 | SIMPLE      | properties | range | Country       | Country | 7
> > > NULL | 74602 | Using where; Using index; Using temporary; Using
> > filesort |
> > 
> > +----+-------------+------------+-------+---------------+---------+---------+------+-------+-----------------------------------------------------------+
> >  1 row in set (0.00 sec)
> 
> 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=mysql-marcsub@progressive-comp.com



[prev in list] [next in list] [prev in thread] [next in thread]