Hi,

try

CREATE INDEX geo_idx ON users(entity_id, user_type);

Filip


Alex Arul napsal(a):
> 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@gmail.com> 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@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
>> >
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=a...rthu@gmail.com
>>
>>
>


--
Filip Krejci <krejcif@gmail.com>

Why use Windows, since there is a door?