and also an index on users.entity_id (will help the join) should solve your
problem.
Thanks
Alex
On 1/24/07, Brent Baisley <brenttech@stripped> wrote:
>
> You should create indexes on the fields you search on most. In this case,
> you are searching on the user_type field, so create an
> index on that field. Otherwise you need to scan the entire table to find
> out which users are of the type you are searching for.
>
>
> ----- Original Message -----
> From: "James Tu" <jtu@stripped>
> To: "MySQL List" <mysql@stripped>
> Sent: Tuesday, January 23, 2007 12:04 PM
> Subject: speeding up a join COUNT
>
>
> > I'm performance testing my 'users' table. It currently has roughly 1M
> user records. The 'geo_entities' table has ~ 250 records.
> >
> > Here's my query.
> >
> > SELECT users.entity_id,
> geo_entities.entity_name, geo_entities.short_code, COUNT( users.entity_id)
> > FROM users, geo_entities
> > WHERE users.user_type = 'user'
> > AND users.entity_id = geo_entities.id
> > GROUP BY entity_id
> > LIMIT 0 , 30
> >
> > It took 51 seconds to execute.
> >
> > Both tables only have an index on their unique record id.
> > Is there a way to speed up this up?
> >
> > -James
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
