Group and group concat, optimization of mysql-query without using the main pk

Question

my example included MYSQL VERSION - 5.6.34 log

Problem : bottom query takes 40 seconds , ORDER_ITEM table

• has 758,423 entries

  AND PAYMENT table

• has 177272 records

And view_name table

• has 2,165,698 entries

  like counting the whole table.

DETAILS HERE: BELOW:

• I have this request, see [1]

• I added SQL_NO_CACHE to test retests when re
  querying.

• . [2], .

• [3]

• [4]

[1]

     SELECT SQL_NO_CACHE
          `payment`.`id`                                                                                    AS id,
          `order_item`.`order_id`                                                                           AS order_id,


          GROUP_CONCAT(DISTINCT (CASE WHEN submission_entry.text = '' OR submission_entry.text IS NULL
            THEN ' '
                                 ELSE submission_entry.text END) ORDER BY question.var DESC SEPARATOR 0x1D) AS buyer,


          event.name                                                                                        AS event,
          COUNT(DISTINCT CASE WHEN (`order_item`.status > 0 OR (
            `order_item`.status != -1 AND `order_item`.status >= -2 AND `payment`.payment_type_id != 8 AND
            payment.make_order_free = 1))
            THEN `order_item`.id
                         ELSE NULL END)                                                                     AS qty,
          payment.currency                                                                                  AS `currency`,
          (SELECT SUM(order_item.sub_total)
           FROM order_item
           WHERE payment_id =
                 payment.id)                                                                                AS sub_total,
          CASE WHEN payment.make_order_free = 1
            THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
          ELSE ROUND(payment.total, 2) END                                                                  AS 'total',
          `payment_type`.`name`                                                                             AS payment_type,
          payment_status.name                                                                               AS status,
          `payment_status`.`id`                                                                             AS status_id,
          DATE_FORMAT(CONVERT_TZ(order_item.`created`, '+0:00', '-8:00'),
                      '%Y-%m-%d %H:%i')                                                                     AS 'created',
          `user`.`name`                                                                                     AS 'agent',
          event.id                                                                                          AS event_id,
          payment.checked,
          DATE_FORMAT(CONVERT_TZ(payment.checked_date, '+0:00', '-8:00'),
                      '%Y-%m-%d %H:%i')                                                                     AS checked_date,
          DATE_FORMAT(CONVERT_TZ(`payment`.`complete_date`, '+0:00', '-8:00'),
                      '%Y-%m-%d %H:%i')                                                                     AS `complete date`,
          `payment`.`delivery_status`                                                                       AS `delivered`
        FROM `order_item`
          INNER JOIN `payment`
            ON payment.id = `order_item`.`payment_id` AND (payment.status > 0.0 OR payment.status = -3.0)
          LEFT JOIN (SELECT
                       sum(`payment_refund`.total) AS `refunds_total`,
                       payment_refunds.payment_id  AS `payment_id`
                     FROM payment
                       INNER JOIN `payment_refunds` ON payment_refunds.payment_id = payment.id
                       INNER JOIN `payment` AS `payment_refund`
                         ON `payment_refund`.id = `payment_refunds`.payment_id_refund
                     GROUP BY `payment_refunds`.payment_id) AS `refunds` ON `refunds`.payment_id = payment.id
#           INNER JOIN event_date_product ON event_date_product.id = order_item.event_date_product_id
#           INNER JOIN event_date ON event_date.id = event_date_product.event_date_id
          INNER JOIN event ON event.id = order_item.event_id
          INNER JOIN payment_status ON payment_status.id = payment.status
          INNER JOIN payment_type ON payment_type.id = payment.payment_type_id
          LEFT JOIN user ON user.id = payment.completed_by
          LEFT JOIN submission_entry ON submission_entry.form_submission_id = `payment`.`form_submission_id`
          LEFT JOIN question ON question.id = submission_entry.question_id AND question.var IN ('name', 'email')
        WHERE 1 = '1' AND (order_item.status > 0.0 OR order_item.status = -2.0)
        GROUP BY `order_item`.`order_id`
        HAVING 1 = '1'
        ORDER BY `order_item`.`order_id` DESC
        LIMIT 10

[2]

 CREATE INDEX order_id
      ON order_item (order_id);

    CREATE INDEX payment_id
      ON order_item (payment_id);

    CREATE INDEX status
      ON order_item (status);

CREATE INDEX payment_type_id
  ON payment (payment_type_id);

CREATE INDEX status
  ON payment (status);

[3]

CREATE TABLE order_item
(
  id                         INT AUTO_INCREMENT
    PRIMARY KEY,
  order_id                   INT                                 NOT NULL,
  form_submission_id         INT                                 NULL,
  status                     DOUBLE DEFAULT '0'                  NULL,
  payment_id                 INT DEFAULT '0'                     NULL
);

CREATE TABLE payment
(
  id                 INT AUTO_INCREMENT,
  payment_type_id    INT                                 NOT NULL,
  status             DOUBLE                              NOT NULL,
  form_submission_id INT                                 NOT NULL,
  PRIMARY KEY (id, payment_type_id)
);

[4] , EXPLAIN HTML

<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>
<table border="1" style="border-collapse:collapse">
<tr><th>id</th><th>select_type</th><th>table</th><th>type</th><th>possible_keys</th><th>key</th><th>key_len</th><th>ref</th><th>rows</th><th>Extra</th></tr>
<tr><td>1</td><td>PRIMARY</td><td>payment_status</td><td>range</td><td>PRIMARY</td><td>PRIMARY</td><td>8</td><td>NULL</td><td>4</td><td>Using where; Using temporary; Using filesort</td></tr>
<tr><td>1</td><td>PRIMARY</td><td>payment</td><td>ref</td><td>PRIMARY,payment_type_id,status</td><td>status</td><td>8</td><td>exp_live_18092017.payment_status.id</td><td>17357</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>payment_type</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment.payment_type_id</td><td>1</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>user</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment.completed_by</td><td>1</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>submission_entry</td><td>ref</td><td>form_submission_id,idx_submission_entry_1</td><td>form_submission_id</td><td>4</td><td>exp_live_18092017.payment.form_submission_id</td><td>2</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>question</td><td>eq_ref</td><td>PRIMARY,var</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.submission_entry.question_id</td><td>1</td><td>Using where</td></tr>
<tr><td>1</td><td>PRIMARY</td><td>order_item</td><td>ref</td><td>status,payment_id</td><td>payment_id</td><td>5</td><td>exp_live_18092017.payment.id</td><td>3</td><td>Using where</td></tr>
<tr><td>1</td><td>PRIMARY</td><td>event</td><td>eq_ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.order_item.event_id</td><td>1</td><td></td></tr>
<tr><td>1</td><td>PRIMARY</td><td>&lt;derived3&gt;</td><td>ref</td><td>key0</td><td>key0</td><td>5</td><td>exp_live_18092017.payment.id</td><td>10</td><td>Using where</td></tr>
<tr><td>3</td><td>DERIVED</td><td>payment_refunds</td><td>index</td><td>payment_id,payment_id_refund</td><td>payment_id</td><td>4</td><td>NULL</td><td>1110</td><td></td></tr>
<tr><td>3</td><td>DERIVED</td><td>payment</td><td>ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment_refunds.payment_id</td><td>1</td><td>Using index</td></tr>
<tr><td>3</td><td>DERIVED</td><td>payment_refund</td><td>ref</td><td>PRIMARY</td><td>PRIMARY</td><td>4</td><td>exp_live_18092017.payment_refunds.payment_id_refund</td><td>1</td><td></td></tr>
<tr><td>2</td><td>DEPENDENT SUBQUERY</td><td>order_item</td><td>ref</td><td>payment_id</td><td>payment_id</td><td>5</td><td>func</td><td>3</td><td></td></tr></table>
</body>
</html>

40 5

1) 1: .

-1: 40 , 25 15

GROUP_CONCAT(DISTINCT (CASE WHEN submission_entry.text = '' OR submission_entry.text IS NULL
    THEN ' '
                         ELSE submission_entry.text END) ORDER BY question.var DESC SEPARATOR 0x1D) AS buyer

40 !

COUNT(DISTINCT CASE WHEN (`order_item`.status > 0 OR (
    `order_item`.status != -1 AND `order_item`.status >= -2 AND `payment`.payment_type_id != 8 AND
    payment.make_order_free = 1))
    THEN `order_item`.id
                 ELSE NULL END)                                                                     AS qty,

, 36 , 4

(SELECT SUM(order_item.sub_total)
   FROM order_item
   WHERE payment_id =
         payment.id)                                                                                AS sub_total,
  CASE WHEN payment.make_order_free = 1
    THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
  ELSE ROUND(payment.total, 2) END                                                                  AS 'total',

Answer 1

HAVING 1=1; , . EXPLAIN SELECT ( html), , .

: PRIMARY KEY (id, payment_type_id). , .

, status DOUBLE: status DOUBLE

, , . , . , , . , . "", , , , .

id PRIMARY KEY ? (, payment)?

"" question.var, LEFT, , . , LEFT JOINs INNER JOINs, .

- (, submission_entry event_date_product) " "? , , .

, SHOW CREATE TABLE .

Answer 2

,

• payment -
• -

i ​​ :

-- -----------------------------------------------------------------------------
-- Summarization of order_item
-- -----------------------------------------------------------------------------

drop temporary table if exists _ord_itm_sub_tot;

create temporary table _ord_itm_sub_tot(
    primary key (payment_id)
)
SELECT
    payment_id,
    --
    COUNT(
        DISTINCT
            CASE
                WHEN(
                        `order_item`.status > 0 OR
                        (
                                `order_item`.status       != -1 AND
                                `order_item`.status       >= -2 AND
                                `payment`.payment_type_id != 8  AND
                                payment.make_order_free = 1
                            )
                    ) THEN `order_item`.id
                      ELSE NULL
            END
    ) AS qty,
    --
    SUM(order_item.sub_total) sub_total
FROM
    order_item
        inner join payment
        on payment.id = order_item.payment_id    
where order_item.status > 0.0 OR order_item.status = -2.0
group by payment_id;

-- -----------------------------------------------------------------------------
-- Summarization of payment_refunds
-- -----------------------------------------------------------------------------

drop temporary table if exists _pay_ref_tot;

create temporary table _pay_ref_tot(
    primary key(payment_id)
)
SELECT
    payment_refunds.payment_id  AS `payment_id`,
    sum(`payment_refund`.total) AS `refunds_total`
FROM
    `payment_refunds`
        INNER JOIN `payment` AS `payment_refund`
        ON `payment_refund`.id = `payment_refunds`.payment_id_refund
GROUP BY `payment_refunds`.payment_id;

-- -----------------------------------------------------------------------------
-- Summarization of submission_entry
-- -----------------------------------------------------------------------------

drop temporary table if exists _sub_ent;

create temporary table _sub_ent(
    primary key(form_submission_id)
)
select 
    submission_entry.form_submission_id,
    GROUP_CONCAT(
        DISTINCT (
            CASE WHEN coalesce(submission_entry.text, '') THEN ' '
                                                          ELSE submission_entry.text
            END
        )
        ORDER BY question.var
        DESC SEPARATOR 0x1D
    ) AS buyer
from 
    submission_entry
        LEFT JOIN question
        ON(
                question.id = submission_entry.question_id
            AND question.var IN ('name', 'email')
        )
group by submission_entry.form_submission_id;

-- -----------------------------------------------------------------------------
-- The result
-- -----------------------------------------------------------------------------

SELECT SQL_NO_CACHE
    `payment`.`id`          AS id,
    `order_item`.`order_id` AS order_id,
    --
    _sub_ent.buyer,
    --
    event.name AS event,
    --
    _ord_itm_sub_tot.qty,
    --
    payment.currency AS `currency`,
    --
    _ord_itm_sub_tot.sub_total,
    --
    CASE
        WHEN payment.make_order_free = 1 THEN ROUND(payment.total + COALESCE(refunds_total, 0), 2)
                                         ELSE ROUND(payment.total, 2)
    END AS 'total',
    --
    `payment_type`.`name`   AS payment_type,
    `payment_status`.`name` AS status,
    `payment_status`.`id`   AS status_id,
    --
    DATE_FORMAT(
        CONVERT_TZ(order_item.`created`, '+0:00', '-8:00'),
        '%Y-%m-%d %H:%i'
    ) AS 'created',
    --
    `user`.`name` AS 'agent',
    event.id      AS event_id,
    payment.checked,
    --
    DATE_FORMAT(CONVERT_TZ(payment.checked_date,  '+0:00', '-8:00'), '%Y-%m-%d %H:%i') AS checked_date,
    DATE_FORMAT(CONVERT_TZ(payment.complete_date, '+0:00', '-8:00'), '%Y-%m-%d %H:%i') AS `complete date`,
    --
    `payment`.`delivery_status` AS `delivered`
FROM
    `payment`
        INNER JOIN(
            `order_item`
                INNER JOIN event
                ON event.id = order_item.event_id
        )
        ON `order_item`.`payment_id` = payment.id
        --
        inner join _ord_itm_sub_tot
        on _ord_itm_sub_tot.payment_id = payment.id
        --
        LEFT JOIN _pay_ref_tot
        on _pay_ref_tot.payment_id = `payment`.id
        --
        INNER JOIN payment_status ON payment_status.id = payment.status
        INNER JOIN payment_type   ON payment_type.id   = payment.payment_type_id
        LEFT  JOIN user           ON user.id           = payment.completed_by
        --
        LEFT JOIN _sub_ent
        on _sub_ent.form_submission_id = `payment`.`form_submission_id`
WHERE
    1 = 1
AND (payment.status > 0.0 OR payment.status = -3.0)
AND (order_item.status > 0.0 OR order_item.status = -2.0)
ORDER BY `order_item`.`order_id` DESC
LIMIT 10

... , , " ".

, .

, , , .

Answer 3

( , . .)

• refunds. CASE.
• . GROUP BY order_item ORDER BY order_item DESC LIMIT 10 . , HAVING 1=1, .
• # 2 " "...

- :

SELECT lots of stuff
    FROM ( query from step 2 ) AS step2
    LEFT JOIN ( ... ) AS refunds  ON step2... = refunds...
    ORDER BY step2.order_item DESC

ORDER BY, GROUP BY LIMIT.

? ...

refunds , , , 10 . 10 .

(: , , , , . , , .)

Answer 4

, , : 10 (LIMIT 10) , (order_item) , . , , .

• WHERE, GROUP BY ORDER BY, , - , . , , - , payment.

• , :

  SELECT o.order_id, o.payment_id
  FROM order_item o
  JOIN payment p
      ON p.id = o.payment_id AND (p.status > 0.0 OR p.status = -3.0)
  WHERE order_item.status > 0.0 OR order_item.status = -2.0
  ORDER BY order_id DESC
  LIMIT 10
  

  , GROUP BY order_id DESC ORDER BY. , BTREE status order_item (status, payment_id).

• , , - , , :

  SELECT order_item.order_id,
    `payment`.`id`,
    GROUP_CONCAT ... -- and so on from the original query
  FROM (
    SELECT o.order_id, o.payment_id
    FROM order_item o
    JOIN payment p
      ON p.id = o.payment_id AND (p.status > 0.0 OR p.status = -3.0)
    WHERE order_item.status > 0.0 OR order_item.status = -2.0
    ORDER BY order_id DESC
    LIMIT 10
  ) as ids
  JOIN order_item ON ids.order_id = order_item.order_id
  JOIN payment ON ids.payment_id = payment.id
  LEFT JOIN ( ... -- and so on
  

, , . , , .

---

UPD1: , LEFT JOIN:

SELECT
  sum(payment.total) AS `refunds_total`,
  refs.payment_id  AS `payment_id`
FROM payment_refunds refs
JOIN payment ON payment.id = refs.payment_id_refund
GROUP BY refs.payment_id

or even replace LEFT JOIN with a correlated subquery, since the correlation will be performed only for these 10 rows (make sure that you use all this query with three columns as a subquery, otherwise the correlation will be calculated for each row in the resulting connection before GROUP BY):

SELECT
      ids.order_id,
      ids.payment_id,
      (SELECT SUM(p.total) 
       FROM payment_refunds refs 
       JOIN payment p 
         ON refs.payment_id_refund = p.id
       WHERE refs.payment_id = ids.payment_id
       ) as refunds_total
    FROM (
      SELECT o.order_id, o.payment_id
      FROM order_item o
      JOIN payment p
        ON p.id = o.payment_id AND (p.status > 0.0 OR p.status = -3.0)
      WHERE order_item.status > 0.0 OR order_item.status = -2.0
      ORDER BY order_id DESC
      LIMIT 10
    ) as ids

You will also need an index (payment_id, payment_id_refund)on payment_refunds, and you can even try the coverage index (payment_id, total)when paying.