Thanks for the information. Before I try to further look into the query, I
would like to know if there is some memory issue. Its strange since the
speed for executing same query differs. Its a Mac G5 with just 1GB ram. I
could see lot of pageouts in the top command. The activity monitor shows
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
?
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 updated select statement i came
Here is the command line explain, earlier i had used phpAdmin
+-++-+--+-+---
--+--+---+
| table | type | possible_keys | key |
Need help on optimizing the select statement:
Table structure
table1
--
id
p_id
table2
--
p_id
out_id
table3
--
out_id
name_id
table4
---
name_id (unique)
prev_id
start_id (unique)
end_id (unique)
Only table4 has unique fields all other fields are not unique.
The
