title: Why is this select statement so slow?
body: <p>This select statement runs super slow. It takes over 10 seconds to complete the execution. Could be much longer but I can't know, because the connection to MySQL times out. That's a separate issue.</p>
  
  <p>Here is the code:</p>
  
  <pre><code>SELECT 
      f.id, f.name, GROUP_CONCAT(DISTINCT (c.firstname)) children
  FROM
      families f,
      children c,
      transactions t
  WHERE
      f.companyid = 1170 AND f.id = t.familyid
          AND f.id = c.familyid
          AND t.transactiontype = 'P'
          AND t.taxdeductible = 'Y'
          AND YEAR(t.date) = 2017
          AND status = 'A'
          OR f.id = 9779432
  GROUP BY f.id
  ORDER BY name;
  </code></pre>
  
  <p>I do have indexes on families.companyid, children.familyid, transactions.transactiontype, transactions.taxdeductible and transactions.date.</p>
  
  <p>Is there any reason why it would be doing a full table scan, despite my indexes?  Or is there other reason why this query runs slowly?</p>
  
  <p><strong>EDIT:  To fill in some of the blanks as per the comments below:</strong></p>
  
  <p><li>The children table has 17MB of data in 73,000 rows.
  <li>The families table has 6MB of data in 56,000 rows
  <li>The transactions table has 83MB of data in 980,000 rows.</p>
  
  <p><strong>CHILDREN TABLE</strong></p>
  
  <pre><code>CREATE TABLE `children` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `familyid` int(10) unsigned DEFAULT '0',
    `companyid` int(11) DEFAULT '0',
    `picture` varchar(250) DEFAULT NULL,
    `stockpicture` varchar(1) DEFAULT 'N',
    `firstname` varchar(250) DEFAULT NULL,
    `lastname` varchar(250) DEFAULT NULL,
    `nickname` varbinary(250) DEFAULT NULL,
    `birthdate` date NOT NULL DEFAULT '0000-00-00',
    `usecustomfee` varchar(1) NOT NULL DEFAULT 'N',
    `usecustomproviderfee` varchar(1) NOT NULL DEFAULT 'N',
    `customfee` decimal(10,2) DEFAULT '0.00',
    `customfeetypecode` varchar(45) DEFAULT 'MONTH',
    `customproviderfee` decimal(10,2) DEFAULT '0.00',
    `customproviderfeetypecode` varchar(45) DEFAULT 'MONTH',
    `usecustomchargeitem` varchar(1) DEFAULT 'N',
    `customchargeitem` int(11) DEFAULT '0',
    `dailyrate` decimal(10,2) DEFAULT '55.00',
    `startdate` date DEFAULT NULL,
    `enddate` date DEFAULT NULL,
    `subsidynotrequired` char(1) NOT NULL DEFAULT 'Y',
    `subsidychildid` varchar(250) DEFAULT NULL,
    `subsidyapplicantid` varchar(250) DEFAULT NULL,
    `subsidynote` text,
    `waitingsince` date DEFAULT NULL,
    `waitingroom` int(11) DEFAULT NULL,
    `waitingtype` varchar(1) DEFAULT 'F',
    `preferredstart` date DEFAULT NULL,
    `registrationdate` date DEFAULT NULL,
    `groupid` int(11) NOT NULL DEFAULT '0',
    `providerisparent` varchar(1) NOT NULL DEFAULT 'N',
    `attendingschool` char(1) NOT NULL DEFAULT 'N',
    `schoolname` varchar(250) DEFAULT NULL,
    `liveswithmother` char(1) NOT NULL DEFAULT 'Y',
    `liveswithfather` char(1) NOT NULL DEFAULT 'Y',
    `liveswithother` char(1) NOT NULL DEFAULT 'N',
    `otherguardian` varchar(250) DEFAULT NULL,
    `sex` char(1) NOT NULL DEFAULT 'M',
    `note` text,
    `archived` char(1) NOT NULL DEFAULT 'N',
    `priorityid` int(11) DEFAULT '0',
    `onlineregistration` varchar(1) NOT NULL DEFAULT 'N',
    `onlineregistrationaccept` varchar(1) NOT NULL DEFAULT 'N',
    `registrationconfirmed` varchar(1) NOT NULL DEFAULT 'N',
    `registrationconfirmeddate` datetime DEFAULT NULL,
    `createddate` datetime DEFAULT NULL,
    `modifieddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `fullpart` varchar(1) DEFAULT 'F',
    `parttimedays` int(11) DEFAULT '10',
    `parttimedaystype` varchar(45) DEFAULT 'D',
    `parttimedaystypecode` varchar(45) DEFAULT 'MONTH',
    `program` varchar(45) DEFAULT 'daycare',
    `registrationnote` varchar(2000) DEFAULT NULL,
    `registrationnoteread` varchar(1) DEFAULT 'N',
    `registrationsubsidy` varchar(45) DEFAULT 'noplan',
    `registrationsubsidydate` datetime DEFAULT NULL,
    `registrationsubsidyamount` decimal(10,2) DEFAULT '0.00',
    PRIMARY KEY (`id`),
    KEY `Familyid` (`familyid`),
    KEY `companyid` (`companyid`),
    KEY `startdate` (`startdate`),
    KEY `enddate` (`enddate`),
    KEY `roomid` (`groupid`),
    KEY `providerisparent` (`providerisparent`)
  ) ENGINE=InnoDB AUTO_INCREMENT=93685 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p><strong>FAMILIES TABLE</strong></p>
  
  <pre><code>CREATE TABLE `families` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `accountnumber` varchar(100) DEFAULT NULL,
    `name` varchar(245) NOT NULL COMMENT 'The account name will typically be the name of the parent responsible for payment',
    `motherid` int(10) unsigned NOT NULL,
    `fatherid` int(10) unsigned NOT NULL,
    `balance` decimal(10,2) NOT NULL DEFAULT '0.00',
    `notes` varchar(2000) DEFAULT NULL,
    `companyid` int(10) unsigned NOT NULL,
    `status` varchar(1) NOT NULL DEFAULT 'A',
    `financialaidrequired` char(1) NOT NULL DEFAULT 'N',
    `intakesurveyid` int(10) unsigned DEFAULT NULL,
    `referralid` int(10) unsigned NOT NULL DEFAULT '0',
    `registrationemailrequired` varchar(1) DEFAULT 'N',
    `registrationemailsent` varchar(1) DEFAULT 'N',
    `registrationemaildate` date DEFAULT NULL,
    `registrationemailaddressfound` varchar(1) DEFAULT NULL,
    `waitinglistemailrequired` varchar(1) DEFAULT 'N',
    `waitinglistemailsent` varchar(1) DEFAULT 'N',
    `waitinglistemaildate` date DEFAULT NULL,
    `waitinglistemailaddressfound` varchar(1) DEFAULT NULL,
    `activationemailrequired` varchar(1) DEFAULT 'N',
    `activationemailsent` varchar(1) DEFAULT 'N',
    `activationemaildate` date DEFAULT NULL,
    `activationemailaddressfound` varchar(1) DEFAULT NULL,
    `createddate` datetime DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `companyid` (`companyid`),
    KEY `intakesurveyid` (`intakesurveyid`),
    KEY `status` (`status`)
  ) ENGINE=InnoDB AUTO_INCREMENT=9803007 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p><strong>TRANSACTIONS TABLE</strong></p>
  
  <pre><code>CREATE TABLE `transactions` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `familyid` int(10) unsigned NOT NULL,
    `date` datetime NOT NULL,
    `transactiontype` varchar(1) NOT NULL DEFAULT 'C' COMMENT '''C'' = Charge, ''P'' = Payment',
    `paymenttype` varchar(3) DEFAULT NULL COMMENT '''DBT'' = Debit, ''CSH'' = Cash, ''CRE'' = Credit Card, ''CHQ'' = Cheque, ''MNY'' = Money Order,''EFT'' = Electronic Funds Transfer',
    `comment` varchar(500) DEFAULT NULL,
    `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
    `reference` varchar(45) DEFAULT NULL,
    `chargeitem` int(10) unsigned DEFAULT '0',
    `taxdeductible` varchar(1) NOT NULL DEFAULT 'Y',
    `payer` varchar(1) DEFAULT 'M',
    `createddate` datetime DEFAULT NULL,
    `modifieddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `Familyid` (`familyid`),
    KEY `Transaction Type` (`transactiontype`),
    KEY `Tax Deductible` (`taxdeductible`),
    KEY `date` (`date`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1013472 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
  </code></pre>