is there any way to optimize this query currently execution time of this query is 585 in this query only two table are use. one was Player detail table containing player info & other was corn_player_data contain scoring record
SELECT
t.players_id,
t.players_name,
t.added_date,
t.total_death AS total_death,
t.total_king AS total_king,
GROUP_CONCAT(t.week_number) AS week_number,
GROUP_CONCAT(t.death) AS Death,
GROUP_CONCAT(t.King) AS King
FROM (SELECT
p.id AS players_id,
p.name AS players_name,
cpd.added_date AS added_date,
cpd4.total_death AS total_death,
cpd5.total_king AS total_king,
WEEK(cpd.added_date) AS week_number,
GROUP_CONCAT(cpd.added_date) AS NewDate,
COUNT(cpd3.result) AS death,
COUNT(cpd2.result) AS King
FROM players AS p
LEFT JOIN cron_players_data AS cpd
ON cpd.player_id = p.id
LEFT JOIN cron_players_data cpd2
ON cpd2.player_id = p.id
AND cpd2.result = 1
AND cpd2.status = 1
AND cpd2.added_date = cpd.added_date
LEFT JOIN cron_players_data cpd3
ON cpd3.player_id = p.id
AND cpd3.result = 2
AND cpd3.status = 1
AND cpd3.added_date = cpd.added_date
LEFT JOIN (SELECT
cron_p_d.player_id,
COUNT(cron_p_d.result) AS total_death
FROM cron_players_data cron_p_d
WHERE cron_p_d.result = 2
AND cron_p_d.status = 1
GROUP BY (cron_p_d.player_id)) AS cpd4
ON cpd4.player_id = p.id
LEFT JOIN (SELECT
cro_p_d.player_id,
COUNT(cro_p_d.result) AS total_king
FROM cron_players_data cro_p_d
WHERE cro_p_d.result = 1
AND cro_p_d.status = 1
GROUP BY (cro_p_d.player_id)) AS cpd5
ON cpd5.player_id = p.id
GROUP BY (p.id),WEEK(cpd.added_date)) AS t
GROUP BY t.players_id
TABLE CREATE TABLE
----------------- ---------------------------------------------------------
cron_players_data CREATE TABLE `cron_players_data` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`cat_id` BIGINT(30) DEFAULT '0',
`sub_cat_id` BIGINT(30) DEFAULT '0',
`team_id` BIGINT(30) DEFAULT '0',
`player_id` BIGINT(30) DEFAULT '0',
`status_team` VARCHAR(225) DEFAULT NULL,
`result` BIGINT(30) DEFAULT '0',
`home` BIGINT(30) UNSIGNED DEFAULT '0',
`guest` BIGINT(30) UNSIGNED DEFAULT '0',
`mvp` BIGINT(30) DEFAULT '0',
`oscar` BIGINT(30) DEFAULT '0',
`wam` BIGINT(30) DEFAULT '0',
`crown` BIGINT(30) DEFAULT '0',
`kiss` BIGINT(30) DEFAULT '0',
`slap` BIGINT(30) DEFAULT '0',
`bomb` BIGINT(30) DEFAULT '0',
`status` TINYINT(1) DEFAULT '0',
`added_date` DATE DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=265 DEFAULT CHARSET=utf8
TABLE CREATE TABLE
------- -----------------------------------------
players CREATE TABLE `players` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`cat_id` BIGINT(30) DEFAULT '0',
`sub_cat_id` BIGINT(30) DEFAULT '0',
`team_id` BIGINT(30) DEFAULT '0',
`name` VARCHAR(225) DEFAULT NULL,
`class_name` VARCHAR(225) DEFAULT NULL,
`image` VARCHAR(225) DEFAULT NULL,
`added_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=147 DEFAULT CHARSET=utf8
output will be like this
players_id players_name added_date total_death total_king week_number Death King
81 Mario Chalmers 2012-06-08 6 7 23,24,25,26 2,3,1,0 0,2,4,1
82 Udonis Haslem 2012-06-09 2 7 23,24,25,26 0,1,0,1 1,1,4,1
83 James Jones 2012-06-09 10 3 23,24,25,26 0,7,3,0 1,0,1,1
84 Chris Bosch 2012-06-09 8 4 23,24,25,26 1,4,2,1 0,2,2,0
85 Dwayne Wade 2012-06-09 6 4 23,24,25,26 1,3,1,1 0,2,2,0
86 Eddie House 2012-06-09 5 4 23,24,25,26 0,3,2,0 1,1,1,1
87 Joel Anthony 2012-06-09 6 6 23,24,25,26 1,3,1,1 1,2,3,0
88 Mike Bibby 2012-06-08 5 9 23,24,25,26 0,3,1,1 2,3,4,0
89 Mike Miller 2012-06-09 7 8 23,24,25,26 1,4,1,1 1,2,4,1
90 Erick Dampier 2012-06-09 11 1 23,24,25,26 2,5,2,2 0,0,1,0
91 Lebron James 2012-06-09 7 4 23,24,25,26 1,4,1,1 0,2,2,0
92 Juwan Howard 2012-06-08 5 4 23,24,25,26 1,2,1,1 0,1,3,0
