Table design issue and complex query questions
Hi All,
Please could anyone advise about the following table design and resulting select query. I am getting a partial Cartesian because the joins are not quite right. These are the tables involved with the relevant indexes and the query follows:
CREATE TABLE IF NOT EXISTS `NewpersistentCentreProfile` (
`pmcp_id` int(11) NOT NULL AUTO_INCREMENT,
`CNA_ID` int(11) NOT NULL,
`YearMonth` date NOT NULL,
`ProfileActive` tinyint(1) NOT NULL DEFAULT '1',
`NetworkTypeID` tinyint(3) NOT NULL,
`CourseID` tinyint(3) NOT NULL,
`ProfileEffectiveFrom` date DEFAULT NULL,
`ProfileEffectiveTo` date DEFAULT NULL,
`RegsTarget` smallint(5) NOT NULL DEFAULT '0',
`ComplsTarget` smallint(5) NOT NULL DEFAULT '0',
`OGSAccessTarget` smallint(5) NOT NULL DEFAULT '0',
UNIQUE KEY `pmcp_id` (`pmcp_id`),
UNIQUE KEY `CNA_ID` (`CNA_ID`,`YearMonth`,`ProfileActive`,`NetworkTypeID`,`CourseID`,`ProfileEffectiveFrom`),
UNIQUE KEY `CNA_ID_2` (`CNA_ID`,`ProfileEffectiveFrom`,`ProfileEffectiveTo`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `persistentNetworkType` (
`NetworkTypeID` tinyint(2) NOT NULL,
`NetworkTypeDesc` varchar(50) NOT NULL,
`NetworkType` varchar(5) NOT NULL,
PRIMARY KEY (`NetworkTypeID`),
UNIQUE KEY `NetworkCode` (`NetworkType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `centresDataByDay` (
`CNA_ID` int(11) NOT NULL,
`dateReference` date NOT NULL,
`myguideRegistrationCount` int(11) NOT NULL DEFAULT '0',
`goOnRegistrationCount` int(11) NOT NULL DEFAULT '0',
`myguideRegsYTD` int(11) NOT NULL DEFAULT '0',
`goOnRegsYTD` int(11) NOT NULL DEFAULT '0',
`myguideOLBStartsCount` int(11) NOT NULL DEFAULT '0',
`goOnOLBStartsCount` int(11) NOT NULL DEFAULT '0',
`myguideOLBStartsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBStartsYTD` int(11) NOT NULL DEFAULT '0',
`myguideFundableOLBComplsCount` int(11) NOT NULL DEFAULT '0',
`goOnFundableOLBComplsCount` int(11) NOT NULL DEFAULT '0',
`myguideOLBCourseComplsCount` int(11) NOT NULL DEFAULT '0',
`goOnOLBCourseComplsCount` int(11) NOT NULL DEFAULT '0',
`myguideFundableOLBComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnFundableOLBComplsYTD` int(11) NOT NULL DEFAULT '0',
`myguideOLBCourseComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBCourseComplsYTD` int(11) NOT NULL DEFAULT '0',
`goOnOLBLADCount` int(11) NOT NULL DEFAULT '0',
UNIQUE KEY `CNA_ID` (`CNA_ID`,`dateReference`),
KEY `dateReference` (`dateReference`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `currentCentreFunding` (
`ccf_id` int(11) NOT NULL AUTO_INCREMENT,
`pcf_id` int(11) NOT NULL DEFAULT '0',
`CNA_ID` int(11) NOT NULL,
`parentCentreRef` int(11) NOT NULL DEFAULT '0',
`NetworkTypeID` tinyint(2) NOT NULL DEFAULT '0',
`FundingStateID` tinyint(1) NOT NULL DEFAULT '0',
`startDatetime` datetime DEFAULT NULL,
`endDatetime` datetime DEFAULT NULL,
`profile` smallint(3) NOT NULL DEFAULT '0',
`dirty` tinyint(1) DEFAULT '0',
UNIQUE KEY `ccf_id` (`ccf_id`),
UNIQUE KEY `CNA_ID_2` (`CNA_ID`),
UNIQUE KEY `CNA_ID` (`CNA_ID`,`startDatetime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `hh_Centre` (
`CentreID` int(11) NOT NULL AUTO_INCREMENT,
`CentreCode` varchar(15) DEFAULT NULL,
`CentreName` varchar(255) NOT NULL,
`PartnerType` enum('Centre Partner','Access Point') DEFAULT 'Centre Partner' COMMENT 'What type of centre is this?',
`OrgTypeID` mediumint(5) DEFAULT NULL,
`LocationID` int(11) NOT NULL,
`Website` varchar(200) DEFAULT NULL,
`OpenToPublic` tinyint(1) NOT NULL,
`WhyNotOpen` varchar(255) DEFAULT NULL,
`CentreActive` tinyint(1) NOT NULL,
`MembershipCentre` tinyint(1) NOT NULL,
`CentreFundingTypeID` smallint(6) NOT NULL DEFAULT '0',
`CreatedBy` varchar(50) NOT NULL,
`CreatedDate` datetime NOT NULL,
`UpdatedBy` varchar(50) DEFAULT NULL,
`UpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`DataSource` varchar(50) DEFAULT NULL COMMENT 'Where did we get this data from?',
`DeliverMaths3To2` tinyint(1) DEFAULT NULL,
`DeliverEnglish3To2` tinyint(1) DEFAULT NULL,
`ReceiptSFA1213` tinyint(1) DEFAULT NULL,
`SFAActivities` varchar(500) DEFAULT NULL,
`OtherFunding1213` varchar(500) DEFAULT NULL,
`AwardingBodyStatus1213` varchar(500) DEFAULT NULL,
`Partnerships` varchar(500) DEFAULT NULL,
`provideAltContact` tinyint(1) DEFAULT NULL,
`AltContactTitle` varchar(9) DEFAULT NULL,
`AltContactFirstName` varchar(100) DEFAULT NULL,
`AltContactLastName` varchar(100) DEFAULT NULL,
`AltContactJobTitle` varchar(100) DEFAULT NULL,
`AltContactEmail` varchar(100) DEFAULT NULL,
`AltContactPhone` varchar(12) DEFAULT NULL,
`doesCloseAtLunch` tinyint(1) DEFAULT NULL,
`lunchClosingTime` varchar(16) DEFAULT NULL,
`PartnershipsInfo` text,
PRIMARY KEY (`CentreID`),
KEY `PartnerType` (`PartnerType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
And this is my query that is built dynamically in Joomla:
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,
uko_2.hh_Centre AS c,
ukon_dw2.currentCentreFunding AS cf,
ukon_dw2.centresDataByDay AS cd
LEFT OUTER JOIN
ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID AND
cd.dateReference BETWEEN p.ProfileEffectiveFrom AND
p.ProfileEffectiveTo
WHERE cd.dateReference >= "2012-07-01" AND
cd.dateReference <= "2012-08-17 23:59:59" AND
cf.NetworkTypeID IN (3) AND
cf.CNA_ID = cd.CNA_ID AND
cf.NetworkTypeID = nt.NetworkTypeID AND
c.CentreID = cf.CNA_ID AND
DATE_FORMAT(p.YearMonth,'%Y-%m') BETWEEN
DATE_FORMAT('2012-07-01 00:00:00','%Y-%m') AND
DATE_FORMAT('2012-08-16 23:59:59', '%Y-%m') AND
p.ProfileActive = 1 AND
p.CourseID IN (1)
GROUP BY DATE_FORMAT(dateReference, "%Y-%m"),
NetworkType,
FundingStateID
1). I am struggling to define the tables and resultant joins between the
cf and cd tables. The cd table possesses a compound key that hold the date i.e 365 record for each centre. The cf table doesn't possess a daily date only a centre (CNA_ID) and startDatetime colum. Hence I can't get a good join.
2).
I am also struggling to define the cd and and p joins
ukon_dw2.centresDataByDay AS cd
LEFT OUTER JOIN
ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID AND
cd.dateReference BETWEEN p.ProfileEffectiveFrom AND
p.ProfileEffectiveTo plus ancillary p cols against constants in the where clause. The cd table hold 365 record for each centre but the profile table is by month. I have added the effectivefrom and effectiveto column to try to address the cartesian like problem.
I have added a number of unique keys to the p table because I am trying to cater for future scalability such as the introduction of profiles for multiple courses at a particular centre.
Many thanks for your help.
Dave