List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:January 24 2007 4:38pm
Subject:Re: speeding up a join COUNT
View as plain text  
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
> 

Thread
speeding up a join COUNTJames Tu23 Jan
  • Re: speeding up a join COUNTBrent Baisley24 Jan
    • Re: speeding up a join COUNTAlex Arul25 Jan
      • Re: speeding up a join COUNTFilip Krejc(í25 Jan
        • Re: speeding up a join COUNTJames Tu25 Jan