0

My query is taking around 540 seconds to get result. Please help me to rewrite it.

Query:

SELECT pd.domain, fd.passkeyid
FROM portfolio.domains AS pd,
     fabulousdomains.domains AS fd
WHERE pd.domain = fd.domain
  AND pd.owner = fd.owner
  AND pd.owner = '15940'
  AND groupid = '28555'
  AND fd.status = 'a'
  AND listingqa NOT IN ( 'default', 'ok' ) ;

Explain Plan:

+----+-------------+-------+--------+---------------------------------------------------------+-------------------+---------+----------------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------------------------------+-------------------+---------+----------------------------------------+--------+-------------+
| 1 | SIMPLE | pd | ref | PRIMARY,owner_idx,groupid_owner_idx,owner_roarcatid_idx | groupid_owner_idx | 8 | const,const | 126280 | Using where |
| 1 | SIMPLE | fd | eq_ref | PRIMARY | PRIMARY | 132 | portfolio.pd.domain,portfolio.pd.owner | 1 | Using where |
+----+-------------+-------+--------+---------------------------------------------------------+---

----------------+---------+----------------------------------------+--------+-------------+ Table structure:

mysql> show create table portfolio.domains\G
*************************** 1. row ***************************
Table: domains
Create Table: CREATE TABLE `domains` (
`domain` char(255) NOT NULL default '',
`owner` int(10) unsigned NOT NULL default '0',
`renewstatus` char(1) NOT NULL default 'u',
`insertdate` date NOT NULL default '0000-00-00',
`createdate` date NOT NULL default '0000-00-00',
`expirydate` date NOT NULL default '0000-00-00',
`refreshdate` date NOT NULL default '0000-00-00',
`lastwrite` date NOT NULL default '0000-00-00',
`lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`registrar` int(11) NOT NULL default '0',
`groupid` int(10) unsigned NOT NULL default '0',
`roarcatid` int(10) unsigned NOT NULL default '0',
`illstatus` enum('notchecked','autoillegal','autook','ok','adult','general','unacceptable') NOT NULL default 'notchecked',
`illstatusdate` datetime NOT NULL default '0000-00-00 00:00:00',
`whitelist` set('unchecked','ok','unknown','trademark','typo','brand') NOT NULL default 'unchecked',
`darkbluesiteid` int(10) unsigned NOT NULL default '0',
`locked` enum('t','f') NOT NULL default 'f',
`googletagstatus` enum('notrequired','unchecked','ok','unacceptable') NOT NULL default 'notrequired',
PRIMARY KEY (`domain`,`owner`),
KEY `owner_idx` (`owner`),
KEY `refreshdate_idx` (`refreshdate`),
KEY `darkbluesiteid_idx` (`darkbluesiteid`),
KEY `insertdate_owner_idx` (`insertdate`,`owner`),
KEY `expiry_owner_registrar_idx` (`expirydate`,`owner`,`registrar`),
KEY `groupid_owner_idx` (`groupid`,`owner`),
KEY `renew_owner_expiry_idx` (`renewstatus`,`owner`,`expirydate`),
KEY `owner_roarcatid_idx` (`owner`,`roarcatid`),
KEY `roarcatid_domain_idx` (`roarcatid`,`domain`),
KEY `illstatus` (`illstatus`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table fabulousdomains.domains\G
*************************** 1. row ***************************
Table: domains
Create Table: CREATE TABLE `domains` (
`domain` char(128) NOT NULL default '',
`owner` int(10) unsigned NOT NULL default '0',
`fixprice` decimal(8,2) NOT NULL default '0.00',
`negprice` decimal(8,2) NOT NULL default '0.00',
`watprice` decimal(8,2) NOT NULL default '0.00',
`rejprice` decimal(8,2) NOT NULL default '0.00',
`price` decimal(8,2) NOT NULL default '0.00',
`pricetype` char(16) NOT NULL default '',
`minoffer` decimal(8,2) NOT NULL default '0.00',
`commission` decimal(8,2) NOT NULL default '0.00',
`commissionbonus` decimal(8,2) NOT NULL default '0.00',
`status` char(1) NOT NULL default '',
`statusdate` date NOT NULL default '0000-00-00',
`lastupdated` datetime NOT NULL default '0000-00-00 00:00:00',
`passkeyid` int(11) NOT NULL default '0',
`visibility` int(10) unsigned NOT NULL default '0',
`customsearch` int(10) unsigned NOT NULL default '0',
`yearlyearn` decimal(8,2) NOT NULL default '0.00',
`quality` float NOT NULL default '0',
`listingqa` enum('unchecked','default','ok','unacceptable','pendingreview') default NULL,
`instantsale` enum('t','f') NOT NULL default 'f',
`transferunlockdate` date NOT NULL default '0000-00-00',
PRIMARY KEY (`domain`,`owner`),
KEY `lastupdated_idx` (`lastupdated`),
KEY `quality_idx` (`quality`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
1
  • Removed the "SQL-Server" tag. Why was that there? – ypercubeᵀᴹ Feb 11 '13 at 9:51
1

First, it would be good if you had written the query using the explicit JOIN syntax and adding the table names in columns:

SELECT pd.domain, fd.passkeyid
FROM  portfolio.domains AS pd
   JOIN
      fabulousdomains.domains AS fd
         ON  pd.domain = fd.domain
         AND pd.owner = fd.owner
WHERE pd.owner = '15940'
  AND pd.groupid = '28555'
  AND fd.status = 'a'
  AND fd.listingqa NOT IN ( 'default', 'ok' ) ;

This can help identify a good indexing pattern for this query. You can try adding these indexes:

portfolio.domains (groupid, owner, domain)

fabulousdomains.domains (status, owner, domain, listingqa, passkeyid)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.