On Mon, 31 Mar 2003 21:13:41 +0100
Tim Bunce <[EMAIL PROTECTED]> wrote:
> On Mon, Mar 31, 2003 at 11:42:37AM -0600, Michael Muratet wrote:
> >
> > 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.
>
> What is the type of the KEY1 field? And what is the definition of
> any index built on that field? (Ideally just post the output of
> "show create table demographics_1;").
>
> Also, what's the exact version of mysql server you're using?
> (Check by using \s in the mysql client.)
>
>
Here's the 'show create...' result...
| demographics_1 |CREATE TABLE `demographics_1` (
`KEY1` char(10) default NULL,
`FIRST_NAME` char(13) default NULL,
`MI` char(1) default NULL,
`LAST_NAME` char(16) default NULL,
`STREET` char(20) default NULL,
`UNIT_DESIGNATOR` char(4) default NULL,
`CITY` char(20) default NULL,
`STATE` char(2) default NULL,
`ZIP` char(5) default NULL,
KEY `KEY1` (`KEY1`)
) TYPE=MyISAM |
The version is
mysql> \s
--------------
mysql Ver 11.18 Distrib 3.23.53a, for pc-linux-gnu (i686)
Connection id: 133
Current database:
Current user: [EMAIL PROTECTED]
Current pager: stdout
Using outfile: ''
Server version: 3.23.53a
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 8 days 1 hour 57 min 7 sec
-------------
It's interesting. It's as fast as it can be with the single quotes, and
without them, rather than failing (as it does on the command line), it
just takes longer.
Thanks
Mike
