Query Optimization of group concat query

Question

I have following database schema -

shop_orders(id, project_id, selling_price, tax_amount, ...)

project_contacts(id, project_id, contact_id, ...)

projects(project_id, project_contact_id, site_contact_id, ...)

Sample Data -

shop_orders(1, 123456, 24.35, 2.34, ...)
project_contacts(1, 123456, 10001, ...)
projects(123456, 10001, 10002, ...)

Desired output -

123456:26.69

I am trying to execute the following query -

SELECT GROUP_CONCAT(CONCAT(project_id, ':', (`selling_price` + `tax_amount`))) AS project_amount 
FROM shop_orders 
WHERE project_id IN (
   SELECT project_id FROM project_contacts WHERE contact_id=10001 
   UNION SELECT project_id FROM projects WHERE project_contact_id=10001 
   UNION SELECT project_id FROM projects WHERE site_contact_id=10001 
)

This query runs fine in demo server which has projects table size around 800, while it doesn't work in production server [i.e. it just keeps on running in zombie state,] which has projects table size ~9000.

Please do let me know any pointers towards optimizing the query.

EDIT

Even this query doesn't return any response -

SELECT CONCAT(project_id, ':', (`selling_price` + `tax_amount`)) AS project_amount 
FROM shop_orders 
WHERE project_id IN (
  SELECT project_id FROM project_contacts WHERE contact_id=10001 
  UNION SELECT project_id FROM projects WHERE project_contact_id=10001 
  UNION SELECT project_id FROM projects WHERE site_contact_id=10001
)

though if i run a query like -

SELECT CONCAT(project_id, ':', (`selling_price` + `tax_amount`)) AS project_amount 
FROM shop_orders WHERE project_id IN (NULL)

it returns answer right away. Also the other union query also runs quite well separately. I can run both the queries separately, but not sure why the single query not returning the output.

`"while it doesn't work"` ??? I guess truncation error: https://stackoverflow.com/a/4469523/5070879 — Lukasz Szozda, Nov 03 '19 at 17:05
@LukaszSzozda I doubt that, coz the original query without group concat doesn't return any response..can you please check the edit — codeomnitrix, Nov 03 '19 at 17:12
Sample data, desired results, and an explanation of the logic would all help. — Gordon Linoff, Nov 03 '19 at 17:16
What version? (There have been significant changes in the optimizer in this area.) Please provide `EXPLAIN SELECT ...` — Rick James, Dec 19 '19 at 00:06

score 1 · Accepted Answer · answered Nov 03 '19 at 17:19

I would start by phrasing the query using EXISTS:

SELECT GROUP_CONCAT(CONCAT(project_id, ':', (`selling_price` + `tax_amount`))) AS project_amount 
FROM shop_orders so
WHERE EXISTS (SELECT 1
              FROM project_contacts pc
              WHERE pc.project_id = so.project_id AND
                    pc.contact_id = 10001
             ) OR
      EXISTS (SELECT 1
              FROM project p
              WHERE p.project_id = so.project_id AND
                    p.project_contact_id = 10001
             ) OR
      EXISTS (SELECT 1
              FROM project p
              WHERE p.project_id = so.project_id AND
                    p.site_contact_id = 10001
             );

For optimal performance, you want the following indexes:

project_contacts(project_id, contact_id)
project(project_id, projecct_contact_id)
project(project_id, site_contact_id)

I should note that the CONCAT() is redundant in the GROUP_CONCAT(), because it takes multiple arguments:

SELECT GROUP_CONCAT(project_id, ':', (`selling_price` + `tax_amount`)) AS project_amount

It worked quite well, exists created so much difference, I will make sure to use this going forward. Thanks a lot — codeomnitrix, Nov 03 '19 at 17:31
even exists query halt once the data reaches 10k rows, I can do the things by firing multiple queries programmatically, but that would be least efficient. — codeomnitrix, Dec 13 '19 at 02:32

Query Optimization of group concat query

1 Answers1