First, the query as listed would not have run as you don't have a "w" alias, and have a trailing comma after the last field. I have rewritten (slightly) for readability and relationships to each other and changed a few "aliases".
SELECT
c.id as customer_id,
c.priority as priority,
c.create_date as create_date,
u.login as login,
c.due_date as due_date,
ts.user_status as status,
cts.status as current_status,
cts.id as status_id,
w.name as workflow_name,
c.summary as summary,
cfi.added_by as acknowledged_by,
concat(du.first_name,' ',du.last_name) as dispatch_user,
concat(disU.first_name,' ',disU.last_name) as acknowledged,
concat(updBy.first_name,' ',updBy.last_name) as manager_last_updated_by,
c.updated_date as updated_date,
concat(u.first_name,' ',u.last_name) as last_updated_by,
ts_new.status_date as new_dt,
tsi.status_date as initial_dt,
tsa.status_date as assigned_dt,
tsinv.status_date as investigating_dt,
tsres.status_date as resolved_dt,
tspc.status_date as pending_closure_dt,
ts_closed.status_date as closed_dt,
IF(uv.status IS NOT NULL, 'Yes', 'No') as user_visit,
fftr.reason as reason,
fftr.notes as resolution
FROM
customer c
join form1 tcv
on (c.id = tcv.customer_id)
join user_status cts
on c.current_status_id = cts.id
join user u
on cts.status_by_id = u.id
left join user_status ts
on cts.status = ts.workflow_state
left join change_info cfi
on c.id = cfi.customer_id
left join user_status ds
on c.id = ds.customer_id
and ds.status = 'FTD'
left join user du
on ds.status_by_id = du.id
left join user_status ftDis
on c.id = ftDis.customer_id
and ftDis.status = 'ASSIGNED - FTD'
left join user_status rx
on c.id = rx.customer_id
and rx.status = 'ASSIGNED - FT'
left join user disU
on rx.status_by_id = disU.id
left join user_status lus
ON c.current_status_id = lus.id
left join user updBy
on lus.status_by_id = updBy.id
left join user_status ts_new
on c.id = ts_new.customer_id
and ts_new.status like 'NEW%'
left join user_status tsi
on c.id = tsi.customer_id
and tsi.status like 'INITIAL SOLUTION%'
left join user_status tsa
on c.id = tsa.customer_id
and tsa.status like 'ASSIGNED%'
left join user_status tsinv
on c.id = tsinv.customer_id
and tsinv.status like 'INVESTIGATING%'
left join user_status tsres
on c.id = tsres.customer_id
and tsres.status like 'RESOLVED%'
left join user_status tspc
on c.id = tspc.customer_id
and tspc.status like 'PENDING CLOSURE%'
left join user_status ts_closed
on c.id = ts_closed.customer_id
and ts_closed.status like 'CLOSED%'
left join user_status uv
on c.id = uv.customer_id
and uv.status = 'RESOLVED – FT'
left join change tc_fftr
ON c.id = tc_fftr.customer_id
and tc_fftr.contents LIKE 'Resolution%'
left join form4 fftr
ON tc_fftr.id = fftr.change_id
WHERE
c.create_date >= DATE_SUB(NOW(),INTERVAL 370 DAY)
GROUP BY
customer_id
I would ensure the following compound indexes on your tables...
table index
customer (create_date, id)
form1 (customer_id)
change_info (customer_id, added_by)
user_status (id, status_by_id, status )
user (id, first_name, last_name)
user_status (customer_id, status, status_date, status_by_id )
change (customer_id, contents, id )
form4 (change_id)
Also, it appears you were not even using the assigned_ft_dis (changed alias to ftDis)
then, in your join after the "form1" of...
join user_status cts
on c.current_status_id = cts.id
join user u
on cts.status_by_id = u.id
left join user_status ts
on cts.status = ts.workflow_state
why are you re-joining based on the cts.status to the user_status table again by alias "ts". IF you have 10k records with status "NEW", you are left-joining to all of those records for the workflow_state. You have nothing associated to the customer ID. But since you are prior in the chain of joins, joined by the current_status_id (which I can only GUESS is the auto-increment ID of the user_status table). Is this a mistake? Are you trying to get some description for the "workflow_state". I believe this one component is a big hit on your performance. Is there a different TABLE (alias "w" for the workflow_state) NOT accurately reflected here?
Now, a possible rewrite. Do a single join to the user status and user table to determine the respective status elements and by whom and doing case/when for the criteria... something like... I am using "US" for User_Status alias and "U2" for generic "User" reference for each corresponding "user status" entry. Now, notice all the case/when instances. Since I am only joining to the status and then user table without specific criteria, I can apply the criteria at the FIELD condition level and either be null, or applicable and grab both the date and who of the action.
SELECT
c.id as customer_id,
c.priority as priority,
c.create_date as create_date,
u.login as login,
c.due_date as due_date,
ts.user_status as status,
cts.status as current_status,
cts.id as status_id,
w.name as workflow_name,
c.summary as summary,
MAX( case when US.status = 'FTD' then US.Status_Date END ) as dispatch_date,
MAX( case when US.status = 'FTD' then concat(U2.first_name,' ',U2.last_name) END ) as dispatch_user,
MAX( case when US.status = 'ASSIGNED - FTD' then US.Status_Date END ) as assigned_date,
MAX( case when US.status = 'ASSIGNED - FTD' then concat(U2.first_name,' ',U2.last_name) END ) as assigned_user,
MAX( case when US.status = 'ASSIGNED - FT' then US.Status_Date END ) as assigned2_date,
MAX( case when US.status = 'ASSIGNED - FT' then concat(U2.first_name,' ',U2.last_name) END ) as assigned2_user,
MAX( case when US.status like 'NEW%' then US.Status_Date END ) as new_date,
MAX( case when US.status like 'NEW%' then concat(U2.first_name,' ',U2.last_name) END ) as new_user,
MAX( case when US.status like 'INITIAL SOLUTION%' then US.Status_Date END ) as solution_date,
MAX( case when US.status like 'INITIAL SOLUTION%' then concat(U2.first_name,' ',U2.last_name) END ) as solution_user,
MAX( case when US.status like 'ASSIGNED%' then US.Status_Date END ) as assigned_date,
MAX( case when US.status like 'ASSIGNED%' then concat(U2.first_name,' ',U2.last_name) END ) as assigned_user,
MAX( case when US.status like 'INVESTIGATING%' then US.Status_Date END ) as investigate_date,
MAX( case when US.status like 'INVESTIGATING%' then concat(U2.first_name,' ',U2.last_name) END ) as investigate_user,
MAX( case when US.status like 'RESOLVED%' then US.Status_Date END ) as resolved_date,
MAX( case when US.status like 'RESOLVED%' then concat(U2.first_name,' ',U2.last_name) END ) as resolved_user,
MAX( case when US.status like 'PENDING CLOSURE%' then US.Status_Date END ) as pendClose_date,
MAX( case when US.status like 'PENDING CLOSURE%' then concat(U2.first_name,' ',U2.last_name) END ) as pendClose_user,
MAX( case when US.status like 'CLOSED%' then US.Status_Date END ) as closed_date,
MAX( case when US.status like 'CLOSED%' then concat(U2.first_name,' ',U2.last_name) END ) as closed_user,
MAX( case when US.status = 'RESOLVED - FT' then US.Status_Date END ) as resolved_date,
MAX( case when US.status = 'RESOLVED - FT' then concat(U2.first_name,' ',U2.last_name) END ) as resolved_user,
c.updated_date as updated_date,
concat(u.first_name,' ',u.last_name) as last_updated_by,
IF(uv.status IS NOT NULL, 'Yes', 'No') as user_visit,
fftr.reason as reason,
fftr.notes as resolution
FROM
customer c
left join user_status US
on c.id = US.customer_id
left join user U2
on US.status_by_id = U2.id
join form1 tcv
on (c.id = tcv.customer_id)
join user_status cts
on c.current_status_id = cts.id
join user u
on cts.status_by_id = u.id
left join user_status ts
on cts.status = ts.workflow_state
left join change_info cfi
on c.id = cfi.customer_id
left join user_status lus
ON c.current_status_id = lus.id
left join user updBy
on lus.status_by_id = updBy.id
left join change tc_fftr
ON c.id = tc_fftr.customer_id
and tc_fftr.contents LIKE 'Resolution%'
left join form4 fftr
ON tc_fftr.id = fftr.change_id
WHERE
c.create_date >= DATE_SUB(NOW(),INTERVAL 370 DAY)
GROUP BY
customer_id
This still may not work completely, but I am sure MOST of it WILL work and accommodate almost all of what you are trying to get even though I am getting a few more details (pair of both dates and who of the corresponding status actions).
