1

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.

sqlfiddle

Any idea to improve the execution time?

1

The derived tables would run somewhat faster if you replaced (in the fund case)

KEY `fund_prog` (`fund_prog`)

with

INDEX(fund_prog, fund_amount, fund_edit_date)

The construct

JOIN ( SELECT ... )
JOIN ( SELECT ... )

was very poorly optimized before 5.6.

Thanks for explaining the dates; using VARCHAR that way is almost as good as the DATE datatype.

Change

ORDER BY (SELECT last_edit_date) DESC

to simply

ORDER BY last_edit_date DESC

I don't know how much it will help, but it might be significant. You will probably see a difference in the EXPLAIN.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.