mysql - 尝试优化通过投票事件列出选民的查询

Question

我正在构建一个查询，根据选民在 votes 表(700 万条记录)中的事件，从 voters 表(100 万条记录)中列出选民。标准如下:

• 大选 (GE) 每年只举行一次，并且只有 2004 年或之后的大选才应计算在内。

• 在前面提到的 GE 中，只有 10% 到 50% 的选民投票的才应该计算在内。

一些不太重要的信息:

• 架构无法更改。它以固定宽度的文本文件形式呈现给我们，通过脚本上传，并用于其他目的。

• 只有当前的活跃选民名单和他们的投票历史是可用的。在我下面的查询中，我包含了一个方程式，每当年份减少 1 时，上限阈值就会减少 10,000 名选民。它并不完美，但它似乎在保留有效 GE 的同时过滤掉不需要的 GE。

例如，如果 2005 年、2006 年、2007 年、2009 年、2010 年和 2011 年有 100,000 到 500,000 名选民投票，那么我只想列出那些年投票的选民。

mysqlfiddle is here

架构如下:

CREATE TABLE IF NOT EXISTS `voters` (
  `CountyEMSID` varchar(9) COLLATE utf8_unicode_ci NOT NULL,
  `LastName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `FirstName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `MiddleInitial` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
  `NameSuffix` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `HouseNumber` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `HouseNumberSuffix` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `ApartmentNumber` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `StreetName` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `City` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `Zip` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `ZipCode4` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `MailingAddress1` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `MailingAddress2` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `MailingAddress3` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `MailingAddress4` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `DOBY` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `DOBM` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `DOBD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `Gender` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
  `Party` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `Other` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `ED` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `AD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `CD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `CO` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `SD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `CC` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `JD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `RegY` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `RegM` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `RegD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `Status` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `VoterType` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
  `StatusChangeY` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `StatusChangeM` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `StatusChangeD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `LastVoted` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `Telephone` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `County` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  KEY `districts` (`CountyEMSID`,`ED`,`AD`,`CD`,`CO`,`SD`,`CC`,`JD`),
  KEY `vsn` (`CountyEMSID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `votes` (
  `CountyEMSID` varchar(9) COLLATE utf8_unicode_ci NOT NULL,
  `County` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `AD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `ED` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `Party` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `ElectionDateY` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `ElectionDateM` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `ElectionDateD` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `ElectionType` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  `VoterType` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
  KEY `CountyEMSID` (`CountyEMSID`),
  KEY `perfect` (`CountyEMSID`,`ElectionDateY`,`ElectionType`),
  KEY `CountyEMSID_2` (`CountyEMSID`,`ElectionDateY`,`ElectionType`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

到目前为止，我有以下查询，它应该只列出 votes 表中选民的唯一 ID (CountyEMSID)。它在 mysqlfiddle 中工作，但在 phpmyadmin 中挂起。

SELECT DISTINCT CountyEMSID
FROM `votes` 
WHERE ElectionDateY IN 
(
SELECT ElectionDateY
FROM `votes`
WHERE ElectionType = 'GE' 
AND ElectionDateY >= 2004 
GROUP BY ElectionDateY 
HAVING COUNT(*) < ((0.5 * (SELECT COUNT(*) FROM `voters`)) - ((YEAR(CURRENT_TIMESTAMP()) - ElectionDateY) * 10000)) 
AND COUNT(*) > (0.1 * (SELECT COUNT(*) FROM `voters`))
)

如果您能帮助优化此查询并对其进行修改，使其从 votes 表中返回所有相应的选民信息，我将不胜感激。

Answer 1

MySQL 对 in 子句的优化很差。基本上，它为处理的每一行重新运行子查询。您应该将计算移动到 from 子句中。这是我的尝试:

select distinct v.*
from votes v join
     (select electiondatey, count(*) as NumYVotes
      from votes v
      group by electiondatey
    ) ey
    on v.electiondatey = ev.electiondatey cross join
    (select count(*) as numvoters from voters) as const
where (NumYVotes < 0.5 * numvoters - year(now()) - ElectionDateY * 10000) and
      (NumYVotes > 0.1 * numvoters)

注意:我还没有测试过，所以它可能有语法错误。