CREATE INDEX geo_idx ON users(entity_id, user_type);
Alex Arul napsal(a):
> and also an index on users.entity_id (will help the join) should solve your
> 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(
>> > 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:
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
Filip Krejci <krejcif@stripped>
Why use Windows, since there is a door?