Início / dba / Perguntas / 34515
Accepted
ashuthosh
Asked: 2013-02-11 23:48:15 +0800 CST

Consulta demorando muito

  • 772

Minha consulta está levando cerca de 540 segundos para obter o resultado. Por favor, me ajude a reescrevê-lo.

Consulta:

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' ) ;

Explique o plano:

+----+-------------+-------+--------+---------------------------------------------------------+-------------------+---------+----------------------------------------+--------+-------------+
| 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 |
+----+-------------+-------+--------+---------------------------------------------------------+---

-----+---------+---------------------- ------------------+--------+-------------+ Estrutura da tabela:

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)
mysql performance

1 respostas

  1. Best Answer

    Primeiro, seria bom se você tivesse escrito a consulta usando a JOINsintaxe explícita e adicionando os nomes das tabelas nas colunas:

    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' ) ;

    Isso pode ajudar a identificar um bom padrão de indexação para esta consulta. Você pode tentar adicionar estes índices:

    portfolio.domains (groupid, owner, domain)

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

relate perguntas