Why is my MYSQL statement timeing out all of a sudden?

My MySQL select statement is now timing out and I don't know why. This statement was working up until today. There is a UNION in the select but it has been working up until now so I don't know what is causing the time out. Below is the select statement. I've tried to add the following to the select statement

select @@global.net_write_timeout;
select @@global.net_read_timeout;
set @@global.net_write_timeout = 999999;
 set @@global.net_read_timeout = 999

Open in new window


 but was told that I don't have the proper permissions and or authority (this site lives on a goDaddy server farm).  I also added the following to my connection string:

CommandTimeout=999999;

    <add name="MySQL" 
         connectionString="User ID=root;Password=xxxxxxx;Host=localhost;Port=3306;Database=trackerdata;Protocol=TCP;CommandTimeout=999999;Compress=false;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0" 
    providerName="System.Data.MySqlClient"/>

Open in new window


but that didn't prevent the timeout either. Please help?

(SELECT
                                        AthleteUid,
                                        AthleteFullName,
                                            SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, TimeIn, TimeOut))) * 60) AS WeekTotal,
                                            RequiredHours, ust.SportTeamName AS SportTeam
                                        FROM
                                            utathleteinfo uai
                                                INNER JOIN
                                            utinout uio on uai.AthleteUid = uio.StudentID
                                                INNER JOIN
                                            utsportteam ust ON ust.utSportTeamUid = uai.SportUid
                                        WHERE
                                            uio.TimeOut IS NOT NULL
                                            AND uio.TimeIn >= '2018-03-04 10:00 AM' AND uio.TimeOut <= '2018-03-09 9:00 PM' GROUP BY 
                                    uai.AthleteUid, 
                                    AthleteFullName, 
                                    WeekTotal, 
                                    RequiredHours, 
                                    SportTeam, 
                                    SemesterTime, 
                                    Coach
                                ORDER BY
                                    SportTeam ASC, 
                                    WeekTotal DESC, 
                                    AthleteFullName) 
                            UNION
                    	(SELECT 
							uai.AthleteUid, uai.AthleteFullName, '00:00:00' AS WeekTotal, uai.RequiredHours, ust.SportTeamName AS SportTeam
						FROM 
							utathleteinfo uai
								LEFT OUTER JOIN
							utinout uio ON uai.AthleteUid = uio.StudentID
								INNER JOIN
							utsportteam ust ON uai.SportUid = ust.utSportTeamUid
						WHERE
							uai.AthleteUid NOT IN 
							(
								SELECT 
									StudentID
								FROM
									utinout
								WHERE
									uio.TimeIn >= '2018-03-04 10:00 AM' 
									AND uio.TimeOut <= '2018-03-09 9:00 PM') AND uai.TotalMinutes = 0 OR (uai.WeekTotal = '0:00' OR uai.WeekTotal = '00:00') GROUP BY uai.AthleteUid ORDER BY SportTeam ASC, WeekTotal DESC) 
							ORDER BY SportTeam, WeekTotal DESC

Open in new window

LVL 1
Michael SterlingWeb Applications DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Remove the order by clauses. Don't order the intermediate results, just order the full final result.
PortletPaulEE Topic AdvisorCommented:
Try this instead, and only order the final result, never apply ordering unless absolutely required.

SELECT
      uai.AthleteUid
    , uai.AthleteFullName
    , COALESCE(uio.WeekTotal, 0) AS WeekTotal
    , uai.RequiredHours
    , ust.SportTeamName AS SportTeam
FROM utathleteinfo uai
INNER JOIN utsportteam ust ON uai.SportUid = ust.utSportTeamUid
LEFT JOIN (SELECT
              StudentID
            , SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, TimeIn, TimeOut))) * 60) AS WeekTotal
          FROM utinout
          WHERE TimeOut IS NOT NULL
          AND TimeIn >= '2018-03-04 10:00 AM'
          AND TimeOut <= '2018-03-09 9:00 PM'
          GROUP BY StudentID) uio ON uai.AthleteUid = uio.StudentID
WHERE uai.TotalMinutes > 0
AND (uai.WeekTotal <> '0:00' OR uai.WeekTotal <> '00:00')
ORDER BY ust.SportTeam, COALESCE(uio.WeekTotal, 0) DESC
;

Open in new window

skullnobrainsCommented:
you need to run "explain" followed by the query. this will let you know what is wrong. post the results if it's not obvious.

not : the clause "AND uai.TotalMinutes = 0 OR (uai.WeekTotal = '0:00' OR uai.WeekTotal = '00:00')" seems weird because whatever is grabbed by the first AND is already grabbed by the first query and whatever is after the first OR happily ignores the date range, and the parenthesis contain the same clause twice... is that what you want ?

actually with proper indexes, having yet another union query for "uai.WeekTotal = '0:00'" might be fastest.

please check that clause really does what you want before checking with explain
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Michael SterlingWeb Applications DeveloperAuthor Commented:
@PortletPaul, & @skullnobrains: I will try both of these suggestions this evening and report back. Thank you.
PortletPaulEE Topic AdvisorCommented:
Is the testing complete?
Michael SterlingWeb Applications DeveloperAuthor Commented:
I did test this, but I'm now getting duplicate records in my query and I have to figure out why. Below is my original query. I've removed the "ORDER BY" clauses as you've suggested. How do I prevent the duplicate records from showing. MY duplicate record looks something like this:

StudentID        WeekTotal
12345678        07:03:00                  <---THIS MEANS THE STUDENT DID ACCUMULATE TIME FOR THE TIME PERIOD INDICATED
12345678        00:00:00                  <---THEN I GET A RECORD SHOWING BLANK TIME FOR THE SAME STUDENT

This doesn't happen with all of the students but with a few of them.

(SELECT AthleteUid, AthleteFullName, SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, TimeIn, TimeOut))) * 60) AS WeekTotal, RequiredHours, ust.SportTeamName AS SportTeam 
FROM 
utathleteinfo uai 
	INNER JOIN 
utinout uio on uai.AthleteUid = uio.StudentID 
	INNER JOIN utsportteam ust ON ust.utSportTeamUid = uai.SportUid 
WHERE 
	uio.TimeOut IS NOT NULL 
	AND uio.TimeIn >= '2018-02-11 6:00 AM' 
	AND uio.TimeOut <= '2018-02-16 9:00 PM' 
GROUP BY 
	uai.AthleteUid, AthleteFullName, WeekTotal, RequiredHours, SportTeam, SemesterTime, Coach) 
UNION 
(SELECT AthleteUid, AthleteFullName, TIME_FORMAT("00:00:00", "%T") AS WeekTotal, RequiredHours, ust.SportTeamName AS SportTeam 
FROM 
	utathleteinfo uai 
		INNER JOIN 
	utinout uio on uai.AthleteUid = uio.StudentID 
		INNER JOIN utsportteam ust ON ust.utSportTeamUid = uai.SportUid 
WHERE 
	WeekTotal = '0:00' 
GROUP BY 
	uai.AthleteUid, AthleteFullName, WeekTotal, RequiredHours, SportTeam, SemesterTime, Coach 
) 
ORDER BY SportTeam, WeekTotal DESC; 

Open in new window

PortletPaulEE Topic AdvisorCommented:
Did you try the alternative query I suggested?
Michael SterlingWeb Applications DeveloperAuthor Commented:
Sorry, I did just now and after running  your query, it seems like its working but its not grabbing all of the students. Its missing about a 3rd of the students and I don't know why yet, I'm going to have to look at subsection of the missing students to see what their records have in common that is causing them to be left out.
PortletPaulEE Topic AdvisorCommented:
Look at the where clause of my query, try removing the second line of that clause.
skullnobrainsCommented:
what about the explain ?

testing by removing clauses will help identifying missing indexes ( in this case the cardinality produced by the end clause might be much less than the start clause's )... but doing the same with an explain would both help identifying other issues and produce actual numbers so actually you probably should do both.

in terms of indexes, your first task is to go through each where clause and tables and identify in which order the tables ( and corresponding clauses ) should be queried, and then check with explain if that is what mysql does. if it does not, you're likely missing an index. in many cases ( but probably not this one ), sorting can also be avoided by reading the rows in the proper order in the first place

just guessing ( and i believe @pp made a similar guess ) but your first table is likely the middle one. meaning that it is likely that mysql uses one of timein/timeout to start with which probably makes it loop through quite a lot of results, or possibly perform an index_merge if both are indexed. another scenario is mysql starting with sportsteam in order to skip the sort which is only sensible if the query produces LOTS of results but the optimizer is sometimes wrong.

it would help knowing which table holds weektotal, the approximate number of lines in each table, and the approximate number of lines corresponding to each where clause run against a single table.
PortletPaulEE Topic AdvisorCommented:
@skullnobrains

The suggestion to remove a line of code refers to my query re-write to see if that resolves a funtional discrepancy compared to the original. It wasn't intended for performance.

But returning to the original question,  an explain plan of the original query (after removing unecessary ordering from it) may be helpful.
Michael SterlingWeb Applications DeveloperAuthor Commented:
@skullnobrains: This is what I got from the explain using the phpMyAdmin database tool provided by goDaddy. My ignorance about what any of this means is profound, to say the least so any knowledge you can pass on will be priceless/invaluable. Please? Also the WeekTotal field is found in the utathleteinfo table.

@PortletPaul: I added "OR uai.WeekTotal <> '00:00:00'", which is almost the polar opposite of what you asked me to do, just to satisfy my own curiosity since the default value of WeekTotal is: "00:00:00".

EXPLAIN SELECT uai.AthleteUid FROM utathleteinfo uai INNER JOIN utsportteam ust ON uai.SportUid = ust.utSportTeamUid LEFT JOIN (SELECT StudentID , SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, TimeIn, TimeOut))) * 60) AS WeekTotal FROM utinout WHERE TimeOut IS NOT NULL AND TimeIn >= '2018-03-04 10:00 AM' AND TimeOut <= '2018-03-09 9:00 PM' GROUP BY StudentID) uio ON uai.AthleteUid = uio.StudentID WHERE uai.TotalMinutes > 0 AND (uai.WeekTotal <> '0:00' OR uai.WeekTotal <> '00:00' OR uai.WeekTotal <> '00:00:00')

Open in new window

mySQLExplain.PNG
skullnobrainsCommented:
@pp : thkx for info. nevertheless this also provides valuable information performance-wise

regarding the exec plan, basically this one is a catastrophic one... which is good because it provides lots of room for improvements
i'm a bit short on time but basically :

- each line corresponds to a different table

- the TYPE column is the type of scan : "eq_ref" means mysql uses a unique index ( which is the fastest ). "ALL" means a full table scan.
- the ROWS column is the approximate number of rows scanned ( as far as the optimizer knows ). you need to multiply all integers in the col to figure out the total number of rows scanned.
- extra is self explanatory ( if not, i can elaborate )
- select type "DERIVED" means you created a temporary table at some point

in your case, mysql does the following
- read the whole utathleteinfo table (non-efficient)
- find the matching rows in utsportteam ( produce a cartesian product of the above and the matching rows ) (ok)
- scan the whole utinout table, apply the where clause on the fly, find the matching rows in the resultset of 2 above operations (looks bad)
- sort on disk ( inefficient, likely avoidable. in this case i cannot even figure out why mysql needed to sort anything )

i have little time to dig in further, but you still have a bunch of redundant stuff in the where clause, and i cannot figure out what the left join on a subquery even does since it is not used in either the where or select clauses... maybe start by writing a SIMPLE query that does whatever you need. maybe try removing the subquery entirely. unless i missed something ( a screenshot is nowhere next to convenient ), you probably can remove the whole LEFT JOIN( ... ) block and grab the same results instantaneously

you definitely should index uai.WeekTotal
...and it would be better to use a numeric field ( and produce minutes in the presentation layer ) rather than store them in sql
note that 12:05 is less than 6:00 when they are sorted so it is obviously less performant but will produce wrong results if you use gt lt comparisons
PortletPaulEE Topic AdvisorCommented:
"distinct" has a very specific meaning in SQL, the following 2 rows ARE "distinct" because WeekTotal is different

StudentID        WeekTotal
12345678        07:03:00
12345678        00:00:00

UNION produces a "distinct" set of rows. To be "distinct" EVERY column is considered (not just the first column or columns).

& This is one of the reasons I was trying to remove UNION from your query (the other reason is it also slows down your query).

As we have no data to test with I can only make suggestions. Please try this query instead:
SELECT
      uai.AthleteUid
    , uai.AthleteFullName
    , COALESCE(uio.WeekTotal, 0) AS WeekTotal
    , uai.RequiredHours
    , ust.SportTeamName AS SportTeam
FROM utathleteinfo uai
INNER JOIN utsportteam ust ON uai.SportUid = ust.utSportTeamUid
LEFT JOIN (SELECT
              StudentID
            , SEC_TO_TIME((SUM(TIMESTAMPDIFF(MINUTE, TimeIn, TimeOut))) * 60) AS WeekTotal
          FROM utinout
          WHERE TimeOut IS NOT NULL
          AND TimeIn >= '2018-03-04 10:00 AM'
          AND TimeOut <= '2018-03-09 9:00 PM'
          GROUP BY StudentID) uio ON uai.AthleteUid = uio.StudentID
ORDER BY ust.SportTeam, COALESCE(uio.WeekTotal, 0) DESC
;

Open in new window

IF this produces the correct result can you also include the explain output for that query.

If it does not produce the correct result we need to know what it isn't doing correctly, and this is where you need to supply "sample data" and the "expected result". Note: "sample data" is NOT the output of the query, it is rows of raw data from EACH table, in the form of inserts if possible or as text we can easily parse to make inserts. We do not need real names or data that is private.
skullnobrainsCommented:
... sorry i missed the uio earlier, easier to spot with the rewrite

question :
- is studentid indexed ?  i'm wondering because mysql should probably start with the subquery and it does not

ideas :
- your subquery should probably be optimised first alone : likely there is no index on either timein or timeout ( index timein if you usually query recent data )
- it is also quite tempting to store a daily or hourly totals in a separate table ( with a trigger or a software operation ) with the studentid as the primary key , ( and an additional clustered index with both columns if you are not using innodb ). if you do that, your query can be changed so the subquery + left join becomes a regular query with an inner join. don't do this before checking other leads and namely missing indexes
Michael SterlingWeb Applications DeveloperAuthor Commented:
@skullnobrains: studentid isn't indexex and I will work on your other suggestions and checks.
skullnobrainsCommented:
are you getting anywhere ?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.