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]
