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]
