and also an index on users.entity_id (will help the join) should solve your problem.
Thanks Alex On 1/24/07, Brent Baisley <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
