Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Removed the "SQL-Server" tag. Why was that there? –  ypercube Feb 11 '13 at 9:51
add comment

1 Answer

up vote 1 down vote accepted

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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