-1

I'm using GROUP_CONCAT() in a MySQL query to convert multiple rows into a single string. It works fine on my local host (XAMPP server) and returns data in no time. But it takes almost 3 mins to return data on production server (AWS server). And local database and production database having exactly 40k rows.

Below is my query:

SELECT T.id,
       ( SELECT GROUP_CONCAT('',TS.id) 
         FROM tbl_transaction as TS 
         WHERE TS.prd_conform = 1 
         AND TS.print NOT IN ('4,5,7') 
         AND TS.delivery_order_no = T.delivery_order_no) as conform_ids_, 
       T.subscription_start_date, 
       T.subscriber_id, 
       T.print,
       T.magazine_id,
       CONCAT(U.first_name,' ',U.last_name) as sname,
       U.id as sm_id,
       M.subscription_year,
       T.transaction_id,
       T.status, 
       T.package_id, 
       S.company_name,
       S.id as client_id,
       S.address_line1,
       S.address_line2,
       CT.name as country,
       S.city,
       S.postal_code,
       Z.short_name,
       C.name as currency,
       T.print_pdf,
       T.print_label,
       T.delivery_order_no
FROM tbl_transaction as T
INNER JOIN transaction_status as TS on TS.id = 3
INNER JOIN magazine_monthly_issue as mmi on mmi.id = T.fk_tag_details
INNER JOIN magazine as M on T.magazine_id = M.id
INNER JOIN currency as C on C.id = T.currency_of_payment
INNER JOIN tbl_clients as S on T.subscriber_id = S.id
LEFT JOIN user as U on U.id = S.salesman
LEFT JOIN zone as Z on Z.id = S.zone
LEFT JOIN country as CT on CT.id = S.country
INNER JOIN tbl_sub_class as SC on T.class_id = SC.id 
WHERE T.prd_conform = 1

Any help will be appreciated. Thank you in advance

1
  • AND TS.print NOT IN ('4,5,7') is probably wrong. You probably need AND TS.print NOT IN (4,5,7) or maybe AND NOT FIND_IN_SET(TS.print ('4,5,7') . Respond to this, then I will consider the Question you have.
    – Rick James
    21 hours ago

3 Answers 3

0

Are you sure the problem in group_concat? If you remove this subselect from the query on production - does it finish in seconds?

If yes - try adding indexes on tbl_transaction table on fields you doing filtering: prd_conform, print, and delivery_order_no.

Same suggestion for all other tables included in the query. You should have indexes on fields you doing join on.

1
  • Thank you for your reply. Yes if I remove this subselect from the query on production they it returns data in seconds. and I have already added indexes on all required fields
    – Vishal
    Apr 3 at 12:39
0

This may assist in expediting query completion.

From early

AND TS.delivery_order_no = T.delivery_order_no) as conform_ids_, 

To

AND T.delivery_order_no = TS.delivery_order_no) as conform_ids_, 

AND

From

INNER JOIN magazine_monthly_issue as mmi on mmi.id = 
T.fk_tag_details
INNER JOIN magazine as M on T.magazine_id = M.id
INNER JOIN currency as C on C.id = T.currency_of_payment
INNER JOIN tbl_clients as S on T.subscriber_id = S.id
LEFT JOIN user as U on U.id = S.salesman
LEFT JOIN zone as Z on Z.id = S.zone
LEFT JOIN country as CT on CT.id = S.country  

To

INNER JOIN magazine_monthly_issue as mmi on T.fk_tag_details = mmi.id 
INNER JOIN magazine as M on T.magazine_id = M.id
INNER JOIN currency as C on T.currency_of_payment = C.id 
INNER JOIN tbl_clients as S on T.subscriber_id = S.id
LEFT JOIN user as U on S.salesman = U.id 
LEFT JOIN zone as Z on S.zone = Z.id 
LEFT JOIN country as CT on S.country = CT.id 

by putting the driving key on the left side of your =

Not tested here. Please post old and run time seconds.

3
  • I don't use MySQL much, but is it expected that the plan can change by changing ON A.x = B.x to ON B.x = A.x ? Apr 5 at 18:24
  • Yes, it can have that effect. Apr 5 at 19:22
  • There is some room for improvement in the optimizer;-) Apr 6 at 21:29
0

Some food for thought (to come up with better guesses, execution plans and ddl including indexes would be needed in your post)

What is the time for:

SELECT T.id,
       ( SELECT GROUP_CONCAT('',TS.id) 
         FROM tbl_transaction as TS 
         WHERE TS.prd_conform = 1 
         AND TS.print NOT IN ('4,5,7') 
         AND TS.delivery_order_no = T.delivery_order_no) as conform_ids_, 
       T.subscription_start_date, 
       T.subscriber_id, 
       T.print,
       T.magazine_id,
       T.transaction_id,
       T.status, 
       T.package_id, 
       T.print_pdf,
       T.print_label,
       T.delivery_order_no
FROM tbl_transaction as T
WHERE T.prd_conform = 1

If that is still slow, does it help to rewrite it to a join?

SELECT T.id, GROUP_CONCAT('',T2.id) as conform_ids_, 
       T.subscription_start_date, 
       T.subscriber_id, 
       T.print,
       T.magazine_id,
       T.transaction_id,
       T.status, 
       T.package_id, 
       T.print_pdf,
       T.print_label,
       T.delivery_order_no
FROM tbl_transaction as T
JOIN tbl_transaction as T2
    USING (delivery_order_no)
WHERE T2.prd_conform = 1 
  AND T2.print NOT IN ('4,5,7')
  AND T.prd_conform = 1
GROUP BY T.id -- newer versions of MySQL can figure out the remaining columns (assuming id is unique).
              -- Otherwise you will have to add remaining columns to GROUP BY

If that helps, you can wrap that in a CTE or a sub-select and add remaining joins and see if it's faster. Example:

WITH agg AS (
    SELECT T.id, GROUP_CONCAT('',T2.id) as conform_ids_, 
           T.subscription_start_date, 
           T.subscriber_id, 
           T.print,
           T.magazine_id,
           T.transaction_id,
           T.status, 
           T.package_id, 
           T.print_pdf,
           T.print_label,
           T.delivery_order_no
    FROM tbl_transaction as T
    JOIN tbl_transaction as T2
        USING (delivery_order_no)
    WHERE T2.prd_conform = 1 
      AND T2.print NOT IN ('4,5,7')
    GROUP BY T.id
)
SELECT agg.id, 
       agg.conform_ids_, 
       agg.subscription_start_date, 
       agg.subscriber_id, 
       agg.print,
       agg.magazine_id,
       CONCAT(U.first_name,' ',U.last_name) as same,
       U.id as sm_id,
       M.subscription_year,
       agg.transaction_id,
       agg.status, 
       agg.package_id, 
       S.company_name,
       S.id as client_id,
       S.address_line1,
       S.address_line2,
       CT.name as country,
       S.city,
       S.postal_code,
       Z.short_name,
       C.name as currency,
       agg.print_pdf,
       agg.print_label,
       agg.delivery_order_no
FROM agg
JOIN transaction_status as TS 
    ON TS.id = 3  -- looks fishy
JOIN magazine_monthly_issue as mmi 
    ON mmi.id = agg.fk_tag_details
JOIN magazine as M 
    ON agg.magazine_id = M.id
JOIN currency as C 
    ON C.id = agg.currency_of_payment
JOIN tbl_clients as S 
    ON agg.subscriber_id = S.id
LEFT JOIN user as U 
    ON U.id = S.salesman
LEFT JOIN zone as Z 
    ON Z.id = S.zone
LEFT JOIN country as CT 
    ON CT.id = S.country
JOIN tbl_sub_class as SC 
    ON T.class_id = SC.id 
      

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