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: <vpendleton@stripped>
To: "Bernd Jagla" <jaglab@stripped>
Cc: "mysql" <mysql@stripped>
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 <jaglab@stripped> 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
