An explain on the select statements reveals that no index is used and that all 133856002 rows are searched.
Do you think that using a new index would make much of difference? I think it has something to do with some system/mysql parameters that can be adjusted. I also don't understand why no one else can log on to the mysql system. If you or someone could explain or point me to a documentation on this it would be pretty close to what I want. Thanks for your help Bernd ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "Bernd Jagla" <[EMAIL PROTECTED]> Cc: "mysql" <[EMAIL PROTECTED]> Sent: Tuesday, March 02, 2004 1:02 PM Subject: Re: no one can log on any more What does the explain plan reveal? >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 3/2/04, 9:05:17 AM, Bernd Jagla <[EMAIL PROTECTED]> wrote regarding no one can log on any more: > Hi there, > I have some weird sql statements that seem to eat up all resources from > mysqld. > Once I have two of them running at the same time no one can log on any > more. Simple queries involving distinct get a out of memory error. > We have tried changing the index buffer size from 256M to 2G, no change. > We have 7G of memory on an IRIX system with 8 cpus. > No replication. > Do you have any idea what is happening? > Thanks a lot > Bernd > the sql statements look like this: > insert into rule2 > select id > from text_uniq_bin > where (substring(rev_rep,1,1)="1") AND > ( > substring(replaced,1,1) + > substring(replaced,2,1) + > substring(replaced,3,1) + > substring(replaced,4,1) + > substring(replaced,5,1) + > substring(replaced,6,1) + > substring(replaced,7,1) + > substring(replaced,8,1) + > substring(replaced,9,1) > ) >2 AND > (substring(replaced,3,1)="0") AND > ( > substring(replaced,12,1) + > substring(replaced,13,1) + > substring(replaced,14,1) + > substring(replaced,15,1) + > substring(replaced,16,1) + > substring(replaced,17,1) + > substring(replaced,18,1) + > substring(replaced,19,1) > ) >5 > ; > AND > insert into rule3 > select id > from text_uniq_bin > where (substring(rev_rep,1,1)="1") AND > ( > substring(replaced,1,1) + > substring(replaced,2,1) + > substring(replaced,3,1) + > substring(replaced,4,1) + > substring(replaced,5,1) + > substring(replaced,6,1) + > substring(replaced,7,1) + > substring(replaced,8,1) + > substring(replaced,9,1) > ) >2 AND > (substring(rev_rep,3,1)="0") AND > ( > substring(replaced,12,1) + > substring(replaced,13,1) + > substring(replaced,14,1) + > substring(replaced,15,1) + > substring(replaced,16,1) + > substring(replaced,17,1) + > substring(replaced,18,1) + > substring(replaced,19,1) > ) <=5 AND > (substring(rev_rep,2,1)="0") AND > ( > substring(replaced,6,1) + > substring(replaced,7,1) + > substring(replaced,8,1) + > substring(replaced,9,1) + > substring(replaced,10,1) + > substring(replaced,11,1) + > substring(replaced,12,1) + > substring(replaced,13,1) + > substring(replaced,14,1) + > substring(replaced,15,1) + > substring(replaced,16,1) + > substring(replaced,17,1) + > substring(replaced,18,1) + > substring(replaced,19,1) > ) >7 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
