I’m having trouble optimizing the query at the end. I’ve included table and inserts. I’ve created the two separate queries and then my merged query attempt.
TABLES and INSERTS:
CREATE TABLE `players` (
`teamid` int(11) DEFAULT NULL,
`jersey` int(11) DEFAULT NULL,
`firstname` varchar(10) DEFAULT NULL,
`lastname` varchar(10) DEFAULT NULL
) ;
INSERT INTO `players` VALUES (1,5,'Joe','Brown'),(1,10,'Ed','Smith'),(1,15,'Bob','Jones'),(2,2,'Redd','Foxx'),(2,4,'Lamont','Sanford'),(2,6,'Grady','Wilson'),(3,3,'Joe','Tinker'),(3,6,'Johnny','Evers'),(3,9,'Frank','Chance'),(4,4,'Chris','Rock'),(4,8,'Eddie','Murphy'),(4,12,'Richard','Pryor');
CREATE TABLE `scoring` (
`editid` int(11) DEFAULT NULL,
`teamid` int(11) DEFAULT NULL,
`jersey` int(11) DEFAULT NULL,
`scoretype` int(11) DEFAULT NULL
) ;
INSERT INTO `scoring` VALUES (1,1,5,1),(1,1,5,1),(1,1,10,1),(1,2,6,1),(2,3,3,1),(2,3,9,1),(2,4,4,1),(2,4,12,1),(3,1,15,1),(3,1,5,1),(3,3,3,1),(3,3,6,1),(3,3,9,1),(4,2,2,1),(4,4,8,1);
CREATE TABLE `sked` (
`editid` int(11) DEFAULT NULL,
`gamedate` date DEFAULT NULL,
`HID` int(11) DEFAULT NULL,
`HScore` int(11) DEFAULT NULL,
`AID` int(11) DEFAULT NULL,
`AScore` int(11) DEFAULT NULL
) ;
INSERT INTO `sked` VALUES (1,'2010-12-01',1,3,2,1),(2,'2010-12-02',3,2,4,2),(3,'2010-12-03',1,2,3,3),(4,'2010-12-04',2,1,4,1);
CREATE TABLE `sponsors` (
`tid` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ;
INSERT INTO `sponsors` VALUES (1,'Generic Group'),(2,'Junk Yard Gang'),(3,'Franklin Adams'),(4,'Comedy Guys');
CREATE TABLE `teams` (
`teamid` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ;
INSERT INTO `teams` VALUES (1,'Browns'),(2,'Reds'),(3,'Blues'),(4,'Green');
This query pulls out my gamescore, team names and sponsor information:
SELECT
editid,
Gamedate,
HID,
HomeSponsor.Name as HTSponsor,
HomeTeam.name as Hometeam,
HScore as HomeScore,
AID,
AwaySponsor.Name as ATSponsor,
AwayTeam.Name Awayteam,
AScore as AwayScore
FROM
sked
INNER JOIN
Sponsors as HomeSponsor
ON
HID=HomeSponsor.TID
INNER JOIN
Sponsors as AwaySponsor
ON
AID=AwaySponsor.TID
INNER JOIN
Teams as HomeTeam
ON
HomeTeam.teamid=HID
INNER JOIN
Teams as AwayTeam
ON
AwayTeam.teamid=AID
ORDER BY
Gamedate,
editid
That results in the following:
1 2010-12-01 1 Generic Group Browns 3 2 Junk Yard Gang Reds 1
2 2010-12-02 3 Franklin Adams Blues 2 4 Comedy Guys Green 2
3 2010-12-03 1 Generic Group Browns 2 3 Franklin Adams Blues 3
4 2010-12-04 2 Junk Yard Gang Reds 1 4 Comedy Guys Green 1
In a separate query I am grabbing the goal scorers per game, but two rows are created per game:
SELECT
dt.editid,
dt.teamid,
GROUP_CONCAT(concat(firstname, ' ', lastname), ' ', goals order by goals desc, lastname) as teamgoals
FROM
(select
scoring.editid,
scoring.teamid,
scoring.jersey,
count(*) goals
from
scoring
where scoretype=1
group by
scoring.editid,
scoring.teamid,
scoring.jersey) as DT
INNER JOIN
players
ON
players.TeamID = DT.Teamid
AND
players.jersey = DT.jersey
GROUP BY
dt.teamid,
dt.editid
ORDER BY
dt.editid,
dt.teamid
1,1,'Joe Brown 2, Ed Smith 1'
1, 2,'Grady Wilson 1'
2,3,'Frank Chance 1, Joe Tinker 1'
2,4,'Richard Pryor 1, Chris Rock 1'
3,1,'Joe Brown 1, Bob Jones 1'
3,3,'Frank Chance 1, Johnny Evers 1, Joe Tinker 1'
4,2,'Redd Foxx 1'
4,4,'Eddie Murphy 1'
So when I’m trying to combine them I wanted to use the following (using MAX to eliminate the NULL for the home or away scorers):
SELECT
editid,
Gamedate,
HID,
HTSponsor,
Hometeam,
HomeScore,
max(homegoals) as hgoals,
AID,
ATSponsor,
Awayteam,
AwayScore,
max(awaygoals) as agoals
FROM
(SELECT
schedule.editid,
Gamedate,
HID,
HomeSponsor.Name as HTSponsor,
HomeTeam.name as Hometeam,
HScore as HomeScore,
CASE WHEN HID=scorers.teamid THEN GROUP_CONCAT(concat(firstname, ' ', lastname), ' ', goals order by goals desc, lastname) ELSE NULL END as homegoals,
AID,
AwaySponsor.Name as ATSponsor,
AwayTeam.Name Awayteam,
AScore as AwayScore,
CASE WHEN AID=scorers.teamid THEN GROUP_CONCAT(concat(firstname, ' ', lastname), ' ', goals order by goals desc, lastname) ELSE NULL END as awaygoals
FROM
schedule
INNER JOIN
Sponsors as HomeSponsor
ON
HID=HomeSponsor.TID
INNER JOIN
Sponsors as AwaySponsor
ON
AID=AwaySponsor.TID
INNER JOIN
Teams as HomeTeam
ON
HomeTeam.teamid=HID
INNER JOIN
Teams as AwayTeam
ON
AwayTeam.teamid=AID
INNER JOIN
(select
scoring.editid,
scoring.teamid,
scoring.jersey,
count(*) goals
from
scoring
where scoretype=1
group by
scoring.editid,
scoring.teamid,
scoring.jersey) as scorers
ON
schedule.editid=scorers.editid
INNER JOIN
players
ON
players.TeamID = scorers.Teamid
AND
players.jersey = scorers.jersey
GROUP BY
scorers.editid,
scorers.teamid
ORDER BY
Gamedate,
schedule.editid
) as dt
group by
editid
My actual tables have about 400 rows for schedule and about 3500 for the scoring table.
Even without the outer most query, the inner one is taking around 8 minutes.
I can post the actual indexes for the tables if need be but don’t think i’m overlooking the indexing so much as making the query to convoluted.
Any suggestions would be helpful.