Re: Table design issue and complex query questions
Hi There,
I have revised the query to but the profile data being retrieved is still not correct!! and yet I believe I have a good join now on the keys between the cd and p tables.
SELECT SQL_CALC_FOUND_ROWS
"---" AS CentreName,
NetworkType,
FundingStateID,
SUM(RegsTarget) AS RegsProfile,
SUM(goOnRegistrationCount) AS goonRegs,
SUM(goOnOLBStartsCount) AS goonStarts,
SUM(ComplsTarget) AS ComplsProfile,
SUM(goOnOLBLADCount) AS goonComps,
DATE_FORMAT(dateReference, "%Y-%m") AS dateReference,
"---" AS CNA_ID
FROM ukon_dw3.persistentNetworkType AS nt
INNER JOIN
ukon_dw2.currentCentreFunding AS cf
ON cf.NetworkTypeID = nt.NetworkTypeID
INNER JOIN
uko_2.hh_Centre AS c
ON c.CentreID = cf.CNA_ID
INNER JOIN
ukon_dw2.centresDataByDay AS cd
ON cf.CNA_ID = cd.CNA_ID
LEFT OUTER JOIN
ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID AND
cd.dateReference >= p.ProfileEffectiveFrom AND
<= p.ProfileEffectiveTo
WHERE cd.dateReference >= "2012-07-01" AND
cd.dateReference <= "2012-08-17 23:59:59" AND //<- the dates here are from the web form
cf.NetworkTypeID IN (3) AND
p.ProfileActive = 1 AND
p.CourseID IN (1)
GROUP BY DATE_FORMAT(dateReference, "%Y-%m"),
NetworkType,
FundingStateID
This is the Query Execution plan (apologies for the formatting):
d select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE nt const PRIMARY PRIMARY 1 const 1 100.00 Using temporary; Using filesort
1 SIMPLE p ALL CNA_ID,CNA_ID_2 NULL NULL NULL 1920 100.00 Using where
1 SIMPLE cf eq_ref CNA_ID_2,CNA_ID CNA_ID_2 4 ukon_dw3.p.CNA_ID 1 100.00 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 ukon_dw3.p.CNA_ID 1 100.00 Using index
1 SIMPLE cd ref CNA_ID,dateReference CNA_ID 4 uko_2.c.CentreID 490 100.00 Using where
2). > KEY `PartnerType` (`PartnerType`)
INDEXes on just a ENUM are almost always useless. What SELECT(s) do you think would use it?
Dave question - if I compare the PartnerType with a constant value (e.g Access Point) then isn't this using the index ?
> UNIQUE KEY `ccf_id` (`ccf_id`),
> UNIQUE KEY `CNA_ID_2` (`CNA_ID`),
Dave question - I can't get a good join between the cf and cd tables the only cols that are common are the CNA - hence I created a unique key on the cf.CNA but yes
the ON cf.CNA_ID = cd.CNA_ID join in the above query will isolated one cf rec but return multiple cd recs (a partial cartesian). not really certain how to resolve this situation
> UNIQUE KEY `CNA_ID` (`CNA_ID`,`startDatetime`)
That's an odd combination of UNIQUE keys. That's 3 constraints that are checked on every INSERT (or UPDATE). The last one adds no extra constraint, but may add a useful index.
Edited 1 time(s). Last edit at 08/20/2012 05:21AM by David Usherwood.