On Mon, 31 Mar 2003 17:05:28 +0100
Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Mon, Mar 31, 2003 at 09:40:24AM -0600, Michael Muratet wrote:
> > On Mon, 31 Mar 2003 10:07:55 +0100
> > Tim Bunce <[EMAIL PROTECTED]> wrote:
> > > > The same query from the command line takes 0.02 seconds. A
> > > > factor of 5,000 tells me I've made a stupid blunder somewhere,
> > > > but I've read and reread the documentation and it all looks OK
> > > > to me.
> > > >
> > >
> > > During the 93 seconds the execute takes, start the mysql client in
> > > another window and do a "show processlist" (or show full
> > > processlist) and try to identify your client connection and what
> > > status it is in.
> > >
> > > Tim.
> >
> > I started the perl script that implements the query, and then
> > checked the state of the process. It spends the entire time in the
> > state"Sending data". Since this is the server side, it's a little
> > unclear to me who's sending what to whom.
>
> Sending to the client. But the server thread actually enters that
> state as soon as it's started executing the query (as all locks etc)
> but before it's sent any results back to the client.
>
> > Any thoughts on where to search for a problem would be appreciated.
>
> If you're on a unix box then using truss (or strace etc) would show
> you when data starts returning from the server.
>
> If you change the query to something trivial ("select 1") is it fast?
> If so, keep changing the query to isolate what makes it slower.
>
>
Greetings
I have discovered that it is the placement of single quotes around the
key value in the query:
> my $rth = $dbh->prepare("SELECT * FROM demographics_1 WHERE
> KEY1=$key");
that makes the difference. The above takes 90 secs.
The below takes milliseconds (as it does from the command line):
> my $rth = $dbh->prepare("SELECT * FROM demographics_1 WHERE
> KEY1='$key'");
Without the single quotes, the command line interface will tell you that
you have a bogus column name. I guess the DBI interface is able to
figure it out, but it takes awhile.
If anyone has any insight to what happens, I'm curious to know.
Thanks.
Mike
