List:General Discussion« Previous MessageNext Message »
From:Filip Krejc(í Date:January 25 2007 1:35pm
Subject:Re: speeding up a join COUNT
View as plain text  
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@stripped> 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@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
>> >
>>
>>
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
> 


-- 
Filip Krejci <krejcif@stripped>

Why use Windows, since there is a door?
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