I have a main table named prog as below:
CREATE TABLE `prog` (
`prog_id` int(11) NOT NULL AUTO_INCREMENT,
`prog_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`prog_edit_date` varchar(16) COLLATE utf8_persian_ci DEFAULT NULL,
`prog_name` text COLLATE utf8_persian_ci NOT NULL,
`prog_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`prog_id`),
KEY `prog_insert_date` (`prog_sabt_date`),
KEY `prog_edit_date` (`prog_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO prog VALUES
(1,'1395-01-01 11:00','1395-01-01 12:00','prog A', 'prog A description'),
(2,'1395-01-02 11:00','1395-01-02 12:00','prog B', 'prog B description'),
(3,'1395-01-03 11:00','1395-01-03 12:00','prog C', 'prog C description');
Because of I need using Jalali calendar in my app I decided to use varchar(16) for date columns and save them in this format : '1395-07-20 12:43'
.
I also have three alike tables which contains several rows corresponding to each prog_id
. They are credit, fund and pay as below:
CREATE TABLE `credit` (
`credit_id` int(11) NOT NULL AUTO_INCREMENT,
`credit_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`credit_edit_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`credit_prog` int(11) NOT NULL,
`credit_amount` bigint(20) NOT NULL,
`credit_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`credit_id`),
KEY `credit_prog` (`credit_prog`),
KEY `credit_insert_date` (`credit_insert_date`),
KEY `credit_edit_date` (`credit_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO credit VALUES
(1,'1395-02-01 11:00','1395-02-01 12:00',1, 100000, 'sample description'),
(2,'1395-02-02 11:00','1395-02-02 12:00',1, 200000, 'sample description'),
(3,'1395-02-03 11:00','1395-02-03 12:00',2, 300000, 'sample description'),
(4,'1395-02-04 11:00','1395-02-04 12:00',2, 400000, 'sample description'),
(5,'1395-02-05 11:00','1395-02-05 12:00',3, 500000, 'sample description'),
(6,'1395-02-06 11:00','1395-02-06 12:00',3, 600000, 'sample description');
CREATE TABLE `fund` (
`fund_id` int(11) NOT NULL AUTO_INCREMENT,
`fund_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`fund_edit_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`fund_prog` int(11) NOT NULL,
`fund_amount` bigint(20) NOT NULL,
`fund_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`fund_id`),
KEY `fund_prog` (`fund_prog`),
KEY `fund_insert_date` (`fund_insert_date`),
KEY `fund_edit_date` (`fund_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO fund VALUES
(1,'1395-03-01 11:00','1395-03-01 12:00',1, 10000, 'sample description'),
(2,'1395-03-02 11:00','1395-03-02 12:00',1, 20000, 'sample description'),
(3,'1395-03-03 11:00','1395-03-03 12:00',2, 30000, 'sample description'),
(4,'1395-03-04 11:00','1395-03-04 12:00',2, 40000, 'sample description'),
(5,'1395-03-05 11:00','1395-03-05 12:00',3, 50000, 'sample description'),
(6,'1395-03-06 11:00','1395-03-06 12:00',3, 60000, 'sample description');
CREATE TABLE `pay` (
`pay_id` int(11) NOT NULL AUTO_INCREMENT,
`pay_insert_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`pay_edit_date` varchar(16) COLLATE utf8_persian_ci NOT NULL,
`pay_prog` int(11) NOT NULL,
`pay_amount` bigint(20) NOT NULL,
`pay_desc` text COLLATE utf8_persian_ci NOT NULL,
PRIMARY KEY (`pay_id`),
KEY `pay_prog` (`pay_prog`),
KEY `pay_insert_date` (`pay_insert_date`),
KEY `pay_edit_date` (`pay_edit_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1;
INSERT INTO pay VALUES
(1,'1395-04-01 11:00','1395-04-01 12:00',1, 1000, 'sample description'),
(2,'1395-04-02 11:00','1395-04-02 12:00',1, 2000, 'sample description'),
(3,'1395-04-03 11:00','1395-04-03 12:00',2, 3000, 'sample description'),
(4,'1395-04-04 11:00','1395-04-04 12:00',2, 4000, 'sample description'),
(5,'1395-04-05 11:00','1395-04-05 12:00',3, 5000, 'sample description'),
(6,'1395-04-06 11:00','1395-04-06 12:00',3, 6000, 'sample description');
Now I want to have each prog row with corresponding sum of credit, fund and pay and also last edit date which should be constructed from all the tables. My query is:
SELECT
prog_id,
GREATEST(IFNULL(credit_edit_date,''),IFNULL(fund_edit_date,''),IFNULL(pay_edit_date,'')) last_edit_date,
prog_name,
credit_amount,
fund_amount,
pay_amount,
prog_desc
FROM prog
LEFT JOIN (
SELECT
credit_prog,
sum(credit_amount) as credit_amount,
max(credit_edit_date) as credit_edit_date
FROM credit GROUP BY credit_prog
) as credit ON credit_prog=prog_id
LEFT JOIN (
SELECT
fund_prog,
sum(fund_amount) as fund_amount,
max(fund_edit_date) as fund_edit_date
FROM fund GROUP BY fund_prog
) as fund ON fund_prog=prog_id
LEFT JOIN (
SELECT
pay_prog,
sum(pay_amount) as pay_amount,
max(pay_edit_date) as pay_edit_date
FROM pay GROUP BY pay_prog
) as pay ON pay_prog=prog_id
ORDER BY last_edit_date DESC
I get the true result, but in real cases with several rows in tables it takes too long to respond. I think that the main problem is the last_edit_date
column and because that MYSQL optimizer cannot consider index on these date fields in subqueries.
Any idea to improve the execution time?
Best Answer
The derived tables would run somewhat faster if you replaced (in the
fund
case)with
The construct
was very poorly optimized before 5.6.
Thanks for explaining the dates; using
VARCHAR
that way is almost as good as theDATE
datatype.Change
to simply
I don't know how much it will help, but it might be significant. You will probably see a difference in the
EXPLAIN
.