Re: complicated select statements and out of memory errors

[Sasha Pachev]

Bernd Jagla wrote:
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
Bernd:

The queries that are giving you trouble are not able to use a key, and I do not 
see a way to re-write them without re-organizing your data in some way. Perhaps 
you could add columns sum1 that will store 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)

and sum2 that will store 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)
and make a key on them. There might be a better way, but it is hard to tell 
without seeing your application.





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 


--
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]