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?
Up to the point that you can cache all the data your server as a whole will ever use, it is better to have more RAM. The question is how much better, and the answer depends on the data access patterns which are nearly impossible to track without experimentation.
A common reason for large variations in the speed of the same query is the caching issue - when the data happens to be cached it goes a lot faster. And, the type of query that is likely to be affected by this is the one that would perform a table scan or read records in a non-sequential order.
So my recommendation is, first, optimize your queryies, and then get more RAM once you cannot optimize them any more.
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
