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.

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