complicated select statements and out of memory errors

[Bernd Jagla]

Hi there,

I have some rather complicated 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