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@esidesign.com>
To: "MySQL List" <mysql@lists.mysql.com>
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=brenttech@gmail.com
>