I have a CRM that I’ve built over the past couple of years that has been working fine. The database is MySQL, and the front end is a web interface. This week, we ‘upgraded’ our hosting plan from a shared server to a cloud server, and immediately we saw a complete collapse of performance on some queries. Routine searches that used to take under a second started taking several minutes and causing the site to crash. I feel like the company is gaslighting me, telling me that it’s an issue with needing to optimize my queries, and not addressing the fact that this drop in performance happened right after they moved our website and database to a different server.

Here is an example query that runs from one of our most frequently accessed pages.

SELECT A.ID, H.Member_Type, H.Member_Subtype, L.Current_Status AS Job_Status
FROM Member_ID A 
LEFT JOIN Member_ID_Status A2 ON A.First_Status = A2.ID 
LEFT JOIN Member_Name B ON A.MemberID = B.MemberID AND B.Current_Status = 'Active' AND B.Preferred = 'Yes' 
LEFT JOIN Member_Election_Districts ET ON A.MemberID = ET.MemberID 
LEFT JOIN Member_Addresses C ON A.MemberID = C.MemberID AND C.Current_Status = 'Active' AND C.Preferred = 'Yes' 
LEFT JOIN Member_Phones D ON A.MemberID = D.MemberID AND D.Current_Status = 'Active' AND D.Preferred = 'Yes' 
LEFT JOIN Member_Emails F ON A.MemberID = F.MemberID AND F.Current_Status = 'Active' AND F.Preferred = 'Yes' 
LEFT JOIN Member_Current_Parameters H ON A.MemberID = H.MemberID 
LEFT JOIN Member_SQ HH ON A.MemberID = HH.MemberID AND HH.SQID='0' AND HH.Current='Yes' 
LEFT JOIN Member_Language I ON A.MemberID = I.MemberID AND I.Current_Status = 'Active' AND I.Preferred = 'Yes' 
LEFT JOIN Member_Leaders J ON A.MemberID = J.MemberID AND J.Current_Status = 'Active' 
LEFT JOIN COPE_ID K ON A.MemberID = K.MemberID AND K.Current_Status = 'Active' 
LEFT JOIN Job_ID L ON A.MemberID = L.MemberID 
LEFT JOIN Job_Location MM ON L.JobID = MM.JobID AND MM.Current_Status = 'Active' 
LEFT JOIN WS_Work_Area MMM ON MM.Worksite_Area = MMM.ID 
LEFT JOIN Job_Start_Date N ON L.JobID = N.JobID AND N.Current_Status = 'Active' 
LEFT JOIN Job_Termination_Date NN ON L.JobID = NN.JobID AND NN.Current_Status = 'Active' 
LEFT JOIN Job_Type O ON L.JobID = O.JobID AND O.Current_Status = 'Active' 
LEFT JOIN Job_Classification P ON L.JobID = P.JobID AND P.Current_Status = 'Active' 
LEFT JOIN Job_EmployeeID PP ON L.JobID = PP.JobID AND PP.Current_Status = 'Active' 
LEFT JOIN Job_Wage PPP ON L.JobID = PPP.JobID AND PPP.Current_Status = 'Active' 
LEFT JOIN Job_Employer_Division Q ON L.JobID = Q.JobID AND Q.Current_Status = 'Active' 
LEFT JOIN Employer_List R on Q.EmpID = R.ID 
LEFT JOIN WS_Name S ON MM.WS_ID = S.WS_ID AND S.Current_Status = 'Active' AND S.Preferred = 'Yes' 
LEFT JOIN WS_Address T ON MM.WS_ID = T.WS_ID AND T.Current_Status = 'Active' AND T.Preferred = 'Yes' 
LEFT JOIN Job_Shift U ON L.JobID = U.JobID AND U.Current_Status = 'Active' 
LEFT JOIN Job_Weekly_Hours WH ON L.JobID = WH.JobID AND WH.Current_Status = 'Active' 
LEFT JOIN Dues_Billing DB ON L.JobID = DB.JobID 
LEFT JOIN COPE_Billing CB ON L.JobID = CB.JobID 
LEFT JOIN Member_Early_Vote MEV ON A.MemberID = MEV.MemberID AND MEV.Election = '' 
LEFT JOIN Contracts_2_Emp_WS X ON Q.EmpID = X.EmpID AND Q.Division = X.Division AND MM.WS_ID = X.WS_ID AND X.Current_Status = 'Active' 
WHERE 1=1 
AND A.Current_Status = 'Active' 
AND Q.EmpID = '2' 
GROUP BY A.MemberID, L.JobID 
HAVING 1=1 AND Job_Status = 'Active';

and here is the EXPLAIN for this query. It executes in 0.575 sec, in spite of joining about 30 tables together.

One of the returned columns on this query is H.Member_Type. I already have a couple of WHERE conditions on the above query, but if I also add a WHERE condition on H.Member_Type (adding “AND H.Member_Type = ‘Member” right before the ‘GROUP BY statement), this query suddenly takes between 5 and 20 minutes to complete. I should note that the column I’m adding the WHERE condition to in this second query is indexed. Here is the EXPLAIN for this query.

Adding to the confusion for me, if I choose to add a WHERE condition to another column, something like ‘First_Name’ which is not an indexed value, the performance of the query is only a little bit slower. (in that case, the query runs in about 2 seconds)

My database does have a lot of tables, and most of my queries do pull from several tables at once, but all join columns are indexed and joins are mostly 1:1, and we’ve never had performance issues before. I also, for my login on our CRM, display query times on the screen, so every day I’m seeing query times on these types of searches, and the worse I’ll see on a complex query with lots of conditions will maybe be 3 or 4 seconds, but simple queries like these never take more than a second.

I’ve tried rebuilding indexes on some of the tables that seemed to be causing problems. That’s had no noticeable impact so far.

I’ve been testing peeling away some of the joins from my query just to see if I can tell if any other specific tables are causing a problem. If I pull a lot of the joins away I can eventually get a query to run quickly with the WHERE condition on Member_Type, but it’s inconsistent.

I’ve mostly been fighting with our hosting company, as I’m convinced that they did something wrong in the migration, or that the server they moved us onto is the problem. But even if the server was under resourced, it doesn’t make sense to me that a query with identical joins would go from 0.5 seconds to over 17 minutes in the screenshot above, simply by adding a basic WHERE condition. My problem is I don’t know what the potential causes could be, so I don’t know what to ask them to check, and they’re acting like this is all just a matter of overly complex queries on my part. I feel like I’m going insane.