2. PHPRunner General questions
  3. query help
This topic is locked

query help

 		11/13/2023 6:21:27 PM
PHPRunner General questions
D
 Dennis L author

I am hoping someone can help me with this query. If I only use one select it will work but slow. When I use all of them it times out and crashes.

SELECT
job_ID,
shop_provided,
weld_type,
(
SELECT

COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND (vt_date > DATE_SUB(NOW(), INTERVAL 1 DAY))
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id

)AS welds_today,
(
SELECT

COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND (vt_date) = CURDATE() - INTERVAL 1 DAY

AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id

)AS welds_yesterday,
(
SELECT

COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND ( YEARWEEK(vt_date) = YEARWEEK(CURRENT_DATE))

AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id

)AS weekly_welds,
(
SELECT

COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O') AND YEARWEEK(vt_date ) = YEARWEEK(CURRENT_DATE - INTERVAL 7 DAY)

AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id

)AS last_week_welds,
(
SELECT

COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O')AND YEAR(vt_date) = YEAR(CURDATE()) AND MONTH(vt_date) = MONTH(CURDATE())

AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id

)AS monthly_welds,
(
SELECT

COUNT(vt_date) AS `COUNT(vt_date)`
FROM shopweldlog sw2
WHERE (shot_req NOT LIKE 'C/O,QLF') AND (shot_req NOT LIKE 'C/O')AND YEAR(vt_date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)

AND MONTH(vt_date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
AND sw1.shop_provided = sw2.shop_provided AND sw1.weld_type = sw2.weld_type AND sw1.job_id = sw2.job_id

)AS last_months_welds

FROM shopweldlog sw1

GROUP BY job_ID, shop_provided, weld_type
dageciDevClub member 11/14/2023

Did you try to use suggestions from AI for example Google Bard or ChatGPT.

Paste in:
Please optimize this query:
(((here goes the query)))

I get some suggestions back, but can't test it because I don't have your database.
C
 		cristi 11/14/2023

The best way to optimize your query is to use composite indexes
I don't know if you have one defined but without it MySql must examine each row in your table in order to count the ones you want and this is - as you can imagine - very slow.
Also:

  • is your server running on a VM (maybe a busy host?) or a physical server?
  • are there a lot of INSERT/UPDATE operations in your table concurrently with your counting queries?
Myr0n 12/17/2023

Does each select work if they run it separately?

At the begining of your select you should specify from which table you want to show those fields, according to your query, those 3 columns exist in sw1 and sw2.
If your query is slow, definitely you need to add composite indexes like cristi said.

SELECT
sw1.job_ID,
sw1.shop_provided,
sw1.weld_type,