Thanks!
OK, here's where my understanding of MySQL and how indices work get
fuzzy.
In my scenario what would the difference between (I tested with
different indices these and included the query times with the EXPLAIN
outputs):
(1) creating separate indices on entity_id and also on user_type -
took 106 seconds (almost twice as long as before for some reason
without these indices)
EXPLAIN output:
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE users ALL user_idx,entity_idx NULL NULL NULL
750106 Using where; Using temporary; Using filesort
1 SIMPLE geo_entities eq_ref PRIMARY PRIMARY 4 users.entity_id 1
vs
(2) creating a multicolumn index with entity_id and user_type - (as
Filip has suggested) - took only 0.4 seconds, wow what a difference !!!
EXPLAIN output:
id select_type table type possible_keys key key_len ref
rows Extra
1 SIMPLE geo_entities ALL PRIMARY NULL NULL NULL 238
Using temporary; Using filesort
1 SIMPLE users ref geo_idx geo_idx 7
geo_entities.id,const 4202 Using where; Using index
vs
(3) with all three indices in place - took 86 seconds.
EXPLAIN output:
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE geo_entities ALL PRIMARY NULL NULL NULL
238 Using temporary; Using filesort
1 SIMPLE users ref user_type_idx,entity_idx,geo_idx geo_idx 7
geo_entities.id,const 4202 Using where; Using index
-James
On Jan 25, 2007, at 8:35 AM, Filip Krejc(í <krejcif@stripped> <Filip
Krejc> wrote:
> 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?
>
