Re: Query fast via mysql CLI, Same query slow via Perl DBI

[gerald_clark]

Daniel J. Conlon wrote:

Hi,

When executing this query through the 'mysql' command line utility, the
result is returned from the database server immediately and the database
server does not create a temporary file.
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type 
FROM domains 
LEFT JOIN accounts USING(username) 
LEFT JOIN zone_records ON zone_records.zone = accounts.username AND
zone_records.type = 'username' 
WHERE domains.host = 1) 
UNION 
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type 
FROM domains 
LEFT JOIN accounts USING(username) 
LEFT JOIN zone_records ON zone_records.zone = domains.domain AND
zone_records.type = 'domain' 
WHERE domains.host = 1) 
ORDER BY domains.domain,zone_records.type 
LIMIT 871;

When executing the exact same query in a Perl script via the DBI
interface, the database server takes minutes to return the results,
using large amounts of CPU whilst computing the results and creating a
temporary file in /var/tmp.
Perhaps perl is taking up so much memory that a temporary file is needed 
to hold the results.

I have turned on query logging and verified that the query logged by the
server is the same when executed with both the mysql and perl clients so
it does not seem that the mysql command line is optimising the query in
any way.
I have restarted the database server between queries to ensure that the
results are not being cached.
The two clients are connecting from the same server using the same
username and password to login.
The structure of the tables concerned is as follows:

CREATE TABLE `accounts` (
 `username` varchar(20) NOT NULL default '',
 `owner` varchar(20) NOT NULL default '',
 `type` varchar(10) default NULL,
 `server` tinyint(3) default NULL,
 `winserver` tinyint(3) default NULL,
 PRIMARY KEY  (`username`),
 KEY `owner` (`owner`),
) TYPE=InnoDB
CREATE TABLE `domains` (
 `domain` varchar(255) NOT NULL default '',
 `username` varchar(20) NOT NULL default '',
 `host` tinyint(1) unsigned NOT NULL default '1',
 PRIMARY KEY  (`domain`),
 KEY `username` (`username`)
) TYPE=InnoDB
CREATE TABLE `zone_records` (
 `zone` varchar(255) NOT NULL default '',
 `type` enum('username','domain','component','default') NOT NULL
default 'username',
 `record` enum('A','MX','CNAME','PTR','NS','SOA') NOT NULL default 'A',
 `ttl` smallint(5) unsigned default NULL,
 `data` varchar(255) NOT NULL default '',
 KEY `zone` (`zone`),
 KEY `zone_type` (`zone`,`type`)
) TYPE=InnoDB
Software versions:

mysql-4.0.12-standard-log
DBI-1.30
DBD-mysql-2.1018
I am really at a loss as to what could be causing this and what to do to
correct the problem. Any assistance or advice you can offer is greatly
appretiated.
Thanks in advance.

Dan Conlon

 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]