Help optimizing query (3 -> 1, I hope)

[crazytonyi]

So the basic idea is this:

 

A user goes to this page because he's working a shift and wants to swap with someone else. The boundaries are:

[*]The user can't swap for a shift at the same time (they have a court date).

[*]The user can't work two shifts at once (so they can't swap for a shift later in the week if they work during that shift).

[*]Other employees can't work two shifts at once (so user can't swap for a shift later in the week if the other guy works during that shift).

[*]The user's shift and the other guy's shift have to be the exact same length (the boss refuses to deal with anything more complicated).

 

First query: pulls all of the user's shifts for the week:

 

$shift_query = "
SELECT *
WHERE
userid = '$userid'
AND shiftstart >= FROM_UNIXTIME('$start_day')
AND shiftend <= FROM_UNIXTIME('$end_day')
";

 

After user chooses a shift, it's returned back to the script as $_POST and the script trys to find a match:

 

First it checks for all shifts that are a)not the user's shifts, b)not overlapping the selected shift, and c)the same length of time as the selected shift:

 

$swap_query = "
SELECT *
FROM postedshifts
AND userid <> '$userid'
AND shiftstart BETWEEN FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$end_day')
AND shiftstart NOT BETWEEN FROM_UNIXTIME('$swap_shift_start') AND FROM_UNIXTIME('$swap_shift_end')
AND shiftend NOT BETWEEN FROM_UNIXTIME('$swap_shift_start') AND FROM_UNIXTIME('$swap_shift_end')
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = $swap_shift_length)
ORDER BY shiftstart
";

 

After all possible shifts are chosen, the script loops through the possible shifts (the results from the query run just before) and runs two queries based on the row:

 

The first one makes sure that the person assigned to the eligble shift doesn't already work during the selected shift.

(In other words, I can't swap my shift on Thursday for the user's Monday shift if I'm already working during the Monday shift.) The query run just before prevented any of my shifts at the same time from being chosen, but it doesn't realize that means I can't swap any of my other shifts either.

 

$busy_query = "
SELECT *
FROM postedshifts
WHERE userid = '$swap_userid'
AND (
(shiftstart BETWEEN FROM_UNIXTIME('$swap_shift_start') AND FROM_UNIXTIME('$swap_shift_end'))
OR (shiftend  BETWEEN FROM_UNIXTIME('$swap_shift_start') AND FROM_UNIXTIME('$swap_shift_end'))
OR (FROM_UNIXTIME('$swap_shift_start') BETWEEN shiftstart AND shiftend)
OR (FROM_UNIXTIME('$swap_shift_end') BETWEEN shiftstart AND shiftend)
)
";

 

 

The second one makes sure that the user isn't already working during the eligible shift.

(Basically, the user wants to swap his Monday shift, I'm working on Thursday, but so is the user, so we can't swap.)

 

$conflict_query = "
SELECT *
FROM postedshifts
WHERE userid = '$userid'
AND (('$startdate' BETWEEN shiftstart AND shiftend
OR '$enddate' BETWEEN shiftstart AND shiftend)
OR (shiftstart BETWEEN '$startdate' AND '$enddate'
OR shiftend BETWEEN '$startdate' AND '$enddate'))
";

 

 

Both queries are run for the eligible shift, and if no results are found that means there is no conflict, the script echo's the result:

 

if ((mysql_num_rows(mysql_query($conflict_query)) == 0) && (mysql_num_rows(mysql_query($busy_query)) == 0))
print $row;

 

So once the loop that checks for busys and conflicts is done, shifts that pass all the tests are output. The user can now email the people on the list, or whatever the script does next.

 

 

So I was looking through the anti-pattern article, and I'm feeling hopeful, but I'm not really sure how (or if I can) get all the eligible shifts in one query instead of having to run a query for each potentially good result to make sure it doesn't conflict in some way.

 

As I see it, I need some way to say "Hey, for that row you are considering, take the user ID and make sure that it doesn't show up on some other row with starttime/endtime conflicts...and oh, while you're at it, make sure that the user ID of the actual user doesn't show up anywhere with an overlap with the current row you are considering..."

 

Honestly, I'm kind of proud I got this far. I feel like I'm way over my head.

 

Thanks!

 

A

 

PS - I hope my php variables are fairly intuitive, but if there is any confusion, I can elaborate.

[crazytonyi]

Okay, don't everybody answer at once.

 

So, I figured out a couple of things:

 

1. The idea I was looking for (I think) was a SELECT subquery. But when I tried those out, the output was SUPER slow. Which makes me think multiple queries are not as horrible as some people like to act. OR that I did the subquries all wrong. Either way, I did get a little bit closer to my goal, so I thought I'd add it to the post before you guys break your legs trying to help...

 

 

Now, it queries who is working at the same time first and I have the script transform the returned user_ids into a list that the next query uses as a black list. This removes the need to check if those people are working during the shift in the loop because those people don't show up in the results. Take a look:

 

$busy_query = "
SELECT DISTINCT userid
FROM shifts
AND (
shiftstart BETWEEN FROM_UNIXTIME('$shift_overlap_start') AND FROM_UNIXTIME('$shift_overlap_end')
OR
FROM_UNIXTIME('$shift_overlap_start') BETWEEN shiftstart AND shiftend
)
";

$busy_result = mysql_query($busy_query);
while ($busy_row = mysql_fetch_assoc($busy_result))
{
$busy_uids[] = "'".$firstbusy_row['userid']."'";
}
$busy_uids = implode(",", $busy_userids);
$swap_query = "SELECT *
FROM shifts
AND userid NOT IN ($busy_uids) 
AND shiftstart BETWEEN  FROM_UNIXTIME('$startday') AND FROM_UNIXTIME('$endday')
AND shiftstart NOT BETWEEN FROM_UNIXTIME('$shift_overlap_start') AND FROM_UNIXTIME('$shift_overlap_end')
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = $swap_shift_length)
ORDER BY shiftstart
";

 

And then goes back to the loop mentioned in my first post, which is now much shorter:

 

$conflict_query = "
SELECT *
FROM shifts 
WHERE userid = '$userid'
AND (
shiftstart BETWEEN FROM_UNIXTIME('$startdate_overlap') AND FROM_UNIXTIME('$enddate_overlap')
OR
FROM_UNIXTIME('$startdate_overlap') BETWEEN shiftstart AND shiftend
)
";

$conflicts = mysql_num_rows(mysql_query($conflict_query));

if ($conflicts == 0)
//print the shift//

 

So I still don't know how to query in advance and say "Hey if this guy is working at the same time, don't grab that row"

 

Help!

 

a

[crazytonyi]

any help?

[crazytonyi]

Is this question too hard or too easy? Someone let me know, because if it's too easy I'll try read up more, but if it's too hard, maybe I can find someone who wants to figure it out with me. thanks!

[crazytonyi]

Okay, so I may have skimmed the "guidelines" of this board. My sincere apologies. Please read this modified version of my post:

 

MySQL server version

 

5.0.60

 

I have three queries, currently. Ideally, I would like to have just one. The main issue is that the third query is actually inside of a while loop, running a query for each result of the second statement. So if there are 50 results, that ends up being 50 queries. I know this can't be the only way.

 

User wants to swap his 7:45 am to 12:15 pm shift on January 5th. The script gets the following:

 

1231163100_1231179300

 

It splits the string into two variables and adds one minute to the start time and subtracts one minute from the end time. This keeps the query from eliminating people whose shift "overlap" by starting when his shift ends. Before the modification, it gets the difference of the two to query only shifts of the same length in the second query.

 

First query looks for any other users in the database working at that time so that those users will be left out of the second query. This is not only to avoid showing joe shifts that are at the same time as the shift he wants to swap, but also because he can't swap for Jane's friday shift because Jane can't work Joe's Monday shift and her own Monday shift simeltaneously, so none of her shifts are potentials...

 

SELECT DISTINCT userid
FROM shifts
AND (
shiftstart BETWEEN FROM_UNIXTIME('1231163160') AND FROM_UNIXTIME('1231179240')
OR
FROM_UNIXTIME('1231163160') BETWEEN shiftstart AND shiftend
)

 

To be clear, the request above asks for usernames of anyone working a shift where the starttime is between the user's shift AND working a shift where the user's starttime time is between thier shift start and end. It took me a while to figure out that this would cover all overlapping shifts.

 

The second query looks for anyone working a shift of the same lenght that workweek. Sorry for the PHP variable, but I'm not sure how else to show that the query omits any user ids found in the above query:

 

FROM shifts
AND userid NOT IN ('$busy_users')
AND shiftstart BETWEEN  FROM_UNIXTIME('1231113600') AND FROM_UNIXTIME('1231718399')
AND (TIME_TO_SEC(TIMEDIFF(shiftend,shiftstart)) = 16200)
ORDER BY shiftstart

 

Finally, with a list of potential shifts, the php script runs a loop through the results and queries the database to see if the user is working already during that shift. Again, the script adjusts the time by one minute on each end to keep adjacent shifts from being omitted.

 

For the purpose of this post, let's assume that the above query found 10 shifts. The loop will run ten queries, of course, but here is query number 8, which is a shift on Thursday the 8th, from 1:30pm to 6:00pm (adjusted, of course) :

 

SELECT *
FROM shifts
WHERE userid = 'joe_smith'
AND (
shiftstart BETWEEN FROM_UNIXTIME('1231421460') AND FROM_UNIXTIME('1231394340')
OR
FROM_UNIXTIME('1231421460') BETWEEN shiftstart AND shiftend
)

 

The query retuned no results, which means that Joe, our swap seeker, is not working that afternoon. The script stores that to echo later. And query 9 shows one result, so it is thrown out and we have some confidence that everything works correctly, just not optimally.

 

So, there you have it. Thanks for reading any and all of this. Again, what the query would ideally do is say "show all shifts of this length, between these dates, where userid "joe smith" is not working at the same time and neither is anyone else who has a shift htat overlaps with these given times (joe's shift he wants to ditch.)

 

Again, thanks for reading. Sorry for the confusion. Thanks to anyone who can help!

 

a

[fenway]

TLDR.. but you should be able to use derived tables.

 

TOPIC LOCKED... duplicate thread.  Don't do that again, please.