memory issue: paging, disk activity, table size -> was: optimizing a select statement

[Karthik Viswanathan]

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 
that there is little free memory (12 -14M) and ~650M of inactive memory and 
~250M of active memory. Read on web that the less free memory is the 
inactive memory will be used. When I run a query (like the one we had 
discussed) there is only a slight difference in this memory status, where 
as the disk activity shows active 'data in', 'read in' . Does this means 
that its out of physical memory and uses swap file? I am not sure how mysql 
uses memory. Couple of tables I read in the query is more than 4GB but the 
query uses only two column of those tables and it will not be more than 1 G 
for sure. I am not sure if I understood correct or not and would like to 
hear your suggestion. Is there any relation between the table size (no of 
rows and file size)  and the system memory needed to get better 
performance? i read on web that its better to have ram more than the 
largest table size. is this true even if the query uses only few columns of 
big tables?

Thanks for your help
Karthik.
At 07:44 PM 2/29/2004, you wrote:
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 f  ON ( e.start_id
BETWEEN f.left_id AND f.end_id )
WHERE (f.name_id =45 OR f.name_id =56)
AND b.id =275 AND a.id != b.id
For some reason you are getting the using temporary on table b, I assume
based on the explain that you have an index called (id) that only has the id
in it.  Here's the first problem.  You are limiting b, by id.  And joining
on p_id.  So mysql is trying to use the index with the combination of both
of them which is combine I assume.  So removing distince really won't help
with this one much, since your indexes will always have a little problems.
INNER JOIN table4 f  ON ( e.start_id
BETWEEN f.left_id AND f.end_id )
This is probably the second biggest problem, you will always get a range.
And ranges will always be slower.  Don't really have a solution without
actually touching the data.
If you really have more than 50 million records this is really bad.
a.id != b.id
I would recommend trying to rewrite your query and just focus on table a and
f.  If you can get rid of them returning the extra 2 and 3 rows, I think
that would solve your problem.
Donny


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]