How to improve my mysql query execution performance

Question

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 (SELECT 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?

If it was me, I think I'd store the ansi date, and handle the conversion elsewhere. — Strawberry, Oct 17 '16 at 07:01
@Strawberry Are you sure change date column types can improve the time significantly? I think that the main problem is indexing in subqueries — Mohammad Hasan Bakhtiarifar, Oct 17 '16 at 07:06
Dup of http://dba.stackexchange.com/questions/152456/how-to-improve-my-mysql-query-execution-performance — Rick James, Oct 19 '16 at 20:18

score 0 · Answer 1 · answered Oct 17 '16 at 09:46

If your subquerys are using indexes other than the primary key (and they probably do), this will actually slow your query down, because it will make MySQL jump back and force in your table instead of reading the whole table into memory once and do the rest there.

Try to add the following indexes:

alter table credit add index 
  idx_credit_p_a_ed(credit_prog, credit_amount, credit_edit_date);
alter table pay add index 
  idx_pay_p_a_ed(pay_prog, pay_amount, pay_edit_date);
alter table fund add index 
  idx_fund_p_a_ed(fund_prog, fund_amount, fund_edit_date);

If you don't use MySQL 5.7 (or if it doesn't take these indexes for some other reason), you may have to force it to use them, so add e.g.

... FROM credit force index (idx_credit_p_a_ed) GROUP BY credit_prog ...

to the subqueries.

You can gain some additional speed if you change your date columns to a 1-byte-per-character datatype, e.g. use latin1_swedish_ci instead of utf8_persian_ci, since you just store dates there. This will reduce your index and table sizes and thus the speed (probably significantly).

Also, change ORDER BY (SELECT last_edit_date) DESC to ORDER BY last_edit_date DESC.

Thanks, I created indices and used force index but it seems that there is not any significant change — Mohammad Hasan Bakhtiarifar, Oct 17 '16 at 11:15
@MohammadHasanBakhtiarifar Can you post the `explain` for your query (just add `explain ` directly infront of `select`)? — Solarflare, Oct 17 '16 at 12:37
But did you also remove the `SELECT` from the `ORDER BY`? It prevents the use of any index. — Rick James, Oct 19 '16 at 20:20

How to improve my mysql query execution performance

1 Answers1