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.
Here is the 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;
I do have indexes on families.companyid, children.familyid, transactions.transactiontype, transactions.taxdeductible and transactions.date.
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?
EDIT: To fill in some of the blanks as per the comments below:
CHILDREN TABLE
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;
FAMILIES TABLE
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;
TRANSACTIONS TABLE
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;
Assuming you mean this (which is how MySQL will interpret it):
(this AND that ...) OR (f.id=...)
Let's use UNION instead of OR. (OR optimizes poorly.)
Let's also use the 'standard' JOIN...ON instead of 'commajoin'.
Let's not hide a column inside a function (YEAR); it inhibits use of an index.
You have already been berated for not saying which table contains status. I see that Hamoon accidentally lost the fact(?) that status is in f. I will assume that.
DISTINCT is not a function, so I removed the parens after it.
I'll pick UNION DISTINCT (slower, but matches OR's semantics) instead of UNION ALL (faster, but might duplicate a row).
I'll move children to an outer SELECT to avoid some potential hiccups.
When GROUP BY and ORDER BY match, the query can run faster. So, assuming id and name are are logically tied together, I think this will give you the same grouping and ordering:
GROUP BY name, id
ORDER BY name, id
Putting all my tips together:
SELECT x.id, x.name,
GROUP_CONCAT(DISTINCT c.firstname) children
FROM (
( SELECT f.id, f.name,
FROM families f
JOIN transactions t ON f.id = t.familyid
WHERE f.companyid = 1170
AND t.transactiontype = 'P'
AND t.taxdeductible = 'Y'
AND t.date >= '2017-01-01'
AND t.date < '2017-01-01' + INTERVAL 1 YEAR
AND f.status = 'A'
)
UNION DISTINCT
( SELECT f.id, f.name
FROM families f
WHERE f.id = 9779432
)
) AS x
JOIN children c ON x.id = c.familyid
GROUP BY x.name, x.id
ORDER BY x.name, x.id
You will need these indexes. The column ordering is usually important.
f: I assume it has PRIMARY KEY(id)
f: (companyid, status) -- in either order
t: (familyid, transactiontype, taxdeductible, date)
t: (transactiontype, taxdeductible, date, familyid)
c: (familyid, firstname)
Some notes:
t -- supply both, thereby letting the Optimizer decide whether to start with f or t.DISTINCT in GROUP_CONCAT may be unnecessary.