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(í <[EMAIL PROTECTED]> <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 <[EMAIL PROTECTED]> 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" <[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]
--
Filip Krejci <[EMAIL PROTECTED]>
Why use Windows, since there is a door?
