here is the updated select statement i came with but still needs improvement
SELECT Distinct (a.id)
From table1 a
INNER JOIN table1 b
USING ( p_id )
INNER JOIN table2 c
USING ( p_id )
INNER JOIN table3 d
USING ( out_id )
INNER JOIN table4 e ON ( d.name_id = e.name_id )
INNER JOIN table4 f
Can you send us a new explain on this version?
Donny
-Original Message-
From: karthik viswanathan [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 29, 2004 4:12 PM
To: [EMAIL PROTECTED]
Subject: Re: optimizing a select statement over a database with 50
million recs
here is the
Thanks for looking at my problem. here is the explain for this version of select
b | ref | id,p_id,combine | combine | 4 | const | 5281 | Using where; Using index;
Using temporary |
c | eq_ref | PRIMARY,p_id | PRIMARY | 4 | b.p_id | 1 | | |
d | ref | name_id,out_id | out_id | 4 | c.out_id |
From looking at this, you have a using temporary, on table b. Which may be
the biggest slow down, it's hard to somewhat tell. I assume you have
indexes on everything... I'm not sure where you are running explain from,
but when I run one it looks like below. You can easily see where problems
Here is the command line explain, earlier i had used phpAdmin
+-++-+--+-+---
--+--+---+
| table | type | possible_keys | key |
So if this is your query and based on the explain this is what I would
recommend.
SELECT Distinct (a.id)
From table1 a
INNER JOIN table1 b
USING ( p_id )
INNER JOIN table2 c
USING ( p_id )
INNER JOIN table3 d
USING ( out_id )
INNER JOIN table4 e ON ( d.name_id = e.name_id )
INNER JOIN table4
