Sum Count Of Weekend Days In Overlapping Periods (MySQL)
I need to get a total count of all Fridays and Saturdays for all overlapping periods against my selected Checkin and Checkout dates.
Table "special_prices" structure:
The current query is using a stored function that works but I need a solution that doesn't use that but rather some raw sql approach for better performance. Current query for context (along with PHP bit):
- I can't change the table structure - having a separate row pear each special price date could solve this problem but I must use the start - end
- special_price "end" column should not be counted in the special_weekend_days
Goal is to find a way to accurately count all Fridays and Saturdays (special_weekend_days) in all overlapping periods.
221 Replies
yikes!
that's extremely hard to read
please, clean up the code and format it properly
by the way, can you show the php too?
I don't have time to look at this thoroughly, but there's two things I want to add here:
- Depending on data size, sometimes it's much easier and better and still plenty fast enough to just do some of the data processing in PHP instead of having to construct some god-awful query for it. That said,
- If you really just need the date in "separate rows" you might be able to join the same table twice, or use a union somehow
there
added the php too
i cant do it with php instead because i need it inside the query, as selects so i can use them
the price expression is needed for price filter
and where does the
$checkOut and $checkIn come from?hmm, then a stored procedure can work too if the query gets too stupid
user params, its a search query for apartments
it slows down the query time by 10.5% on average
its an optimized function that uses math instead of date functions for faster calculation
does that actually matter in this case?
what exactly do you want from this?
(i ask cause very, very often that performance hit doesn't actually matter)
a way to calculate it in a faster raw sql way without using the function
calculate what?
i mean yeah if i were the one to decide it would be the solution but my boss / senior dev doesnt like it so
read the post
i read it, i dont understand it
it says what you're trying to write, not what you need
i need to calculate a total number of fridays and saturdays for overlaps
overlaps of what?
i gave that query with the stored function only as an example, instead of it there needs to be another solution for geting the count, which im asking here
overlaps of special price periods
which i calculated
and the special price periods are in a database?
table
i just need to get the total number of fridays and saturdays that fall in those overlaps
yes
start
end
price
and start and end may be a weekend?
i have a checkin and a checkout from the search params
within that selected period multiple special price periods can fall in / get caught within the selected period
end must be excluded from the count
welp, that complicates things
how exactly
you can't use
betweeni could do something like -1 or - 1 day
stuff like that
true
also its mysql 5.7, forgot to mention
some functions aint there
like date_period
or some like that
i have a diff of days within each overlap, i just need a way to iterate trough each overlap period and see if there weekday 4 or 5
so, between date A and date B, you want all the special prices that have a weekend, and count the weekends total?
all the special prices * (doesnt matter if they have a weekend)
but still i need to know the number of weekend days in case there are any
calculate in php
cant
is it the number of weekend days between 2 dates?
for each row?
lets say that
yeah
an overlap
special price period can clash with user selected period and that created an overlap
clash? overlap?
i dont know how to explain it in a different way
there are special price periods
each periods is saved to db, it has a start, end and a price (not relevant here)
now when user selected a start and end of his trip / stay, that range can include dates that are within those special price periods
those dates that are included in both user selection and special price periods are overlaps
those dates have the special price and the rest have the regular price
now i have a total number of weekend days for the selected period, but from that number i need to takeaway the number of weekend days that have a special price, so that those special price weekends dont get the regular weekend price
basically, you want to calculate the price from x to y:
- may be week days
- may be weekends
- may be weekends with special prices
yes
and where's the price for week and weekend days?
the only missing piece from the puzzle is the number of special weekend days
the regular price?
dear lord
pr. $column is the regular price
a.weekend_extra_price is the base weekend price
thast not relevant here tho
i just need a way to get number of weekend days for each special price period overlap (start - end)
what's up with the weird
coalesce calls sprinkled all over?will handle that later, not important for the current problem
makes things needlessly harder to read
can you make a view?
the only thing relevant to the problem is the join query where i get the stuff from special_prices
im taking an holistic approach, like how i take with everything
wdym a view
a view
wdym a view
of what
where
https://dev.mysql.com/doc/refman/5.7/en/views.html
of the table with the special prices
oh
im trying to think of a way to just get all the weekends in the table
mysql isn't made for this type of thing
sorry for the wait
is this what u asked for
im not sure how is that useful
it doesnt matter how many there are in the table
as those periods are full "uncut" periods
exactly, they are multi-day periods
im basically cutting them depending on the user selected period
and those cut out parts (can be full too if user selectes same dates as the period) are getting into the sql datediff
i feel its just one more step, its almost there
in this case, i feel like you're trying to put everything in the square hole
just do the calculation in php
so, after getting the results?
yes
then i do the foreach and for each apartment i add the calculated price
basically
then i have to move the price filter out of the sql
that was one of the first ideas
i mean
it can be one of the last resort solutions
i've been pulling my hairs and all solutions i found use some trickery with temporary tables
and how much do those slow down the query
that's not the problem
probably even more than the stored functions
its late too
1am for me
the problem is that you can only create a single temporary table per session, and using fpm and other performance-saving techniques can keep the same session open
that means: you can run the function once, at all time, for many users
yeah
that's not a solution: that's shooting yourself in the foot
you can accelerate the query if you make use of indexes
if you make an index with
apartment_id and start and end, your queries don't touch the table and run fasteri have them in the most places, (on places i worked on, its a legacy project)
about 10y old
11
ah, that probably uses just modphp5, for apache
still, it's a dangerous way of living
for start and end im using a composite index
that's a terrible index, if you use the
apartment_id as wellnow, why apartment_id is not in it is something i should ask myself after im done fighting this problem
how would u index it?
given this case and structure
all 3, unique index, without the
id columnwhy unique indexes?
im always using both start and end in the queries
so you can't add the same period twice for the same apartment id
give me a sec
ii see i see
u right i dont need a composite here
you do, with all 3
actually that would be the case if apartment_id was in the composite
if all 3 are in the composite then it cant have same period for same apartment id
right
yes but
it has a performance benefict: any query for the start, end and apartment id will be answered from the index, without scanning the table
that can make things REALLY fast
yeah
if you need other columns, then it still needs to scan the table, but it still has the benefit of not allowing repeated periods
for the same apartment id
yeah yeah i see
lets get to the weekend days tho,
instead of
i need a different way
least is min and greatest is maxyes
actually for easier read and thinking
you can look at GREATEST("' . $checkIn . '", start), LEAST("' . $checkOut . '", end) as overlap_start and overlap_end
as thats whats important, those are start and end of the overlap it got from the each row
so, you have a function that already does the hard part
you can just use the same, but with a subquery
the code of the stored function, but instead of a function, as a subquery?
wait maybe sharing the function code can help
in fact, all you need is a
sum((select SUM(count_weekend_days(...)) from special_weekend_days where start between A and B or end between A and B))
or something like thisthe problem is, count_weekend_days needs to be replaced with a different solution
the solution is php
i see
im worried about the performance hit tho
you're using mysql 5.7
more compute per each query
if you were worried about performance, you would use mariadb 10.11 or newer
well yeah ofc
also, just cache stuff too
how about you have a table where you insert the number of weekends between the dates?
but its not the changes im allowed to make
and then you just do maths?
i cant just pile up more garbage on the already big pile
add the index, and the performance should be even
also, did you even measure performance?
that sounds good
i do
0.45s per query call on average
did you measure where the performance issues are?
well, since i added the stored function approach, along with the new join with special prices, it slowed down to 0.53s on average
from top of my head tho, but something like that
the 2% slowdown comes from the join
then try to do the calculations in php
so the fumctiom is the rest of the hit
so when i do that, in php
i need to apply the price filter in php too
well that sounds like one of the better solutions
in my case
just get the correct data from the database
you don't need to filter anything in php
just calculate
to calculate the price accurately i must get that special weekend days count
you need to get the data
that's it
yeah
just get the data
and then calculate it all in php
got it
yes, that's it
so i need the price filter in php
after getting the price calculations in php
you don't need to filter anything
just calculate everything
i have a price filter in the system
so?
before that i need to get the averege price per day
then get it
and it depends on the calculations
i don't get the point but okay
yeah, you need to calculate it
so i cannot apply the price filter before it, in sql
what filter?
but i need to filter the php data
price filter
for what?
doesnt matter tho, not even relevant
for apartments
the price filter for apartments takes the average price per day
you're getting the price of EVERYTHING?
yes
its not exactly everything
no wonder it is so slow
but the prices that are needed for the guests
its how booking systems work, cant go without it
so in short, its base price + weekend price + special price (prices users are allowed to set for specific dates, holidays, events and whatnot)
the total price for period is calculated first and from that i get the average price per day
i have an idea for you
a strong rope
right
gotta sleep
almost 2 am
well thanks for the help and the advices
i had an idea but it doesn't work
what was the idea
filter out the dates gradually
do you use mysqli or pdo?
i need a bit more
if you don't, the idea stops here
PDO
okay
laravel 6
and the default laravel stuff
eloquent query builder
have a prepared statement to get the regular price and one to get the stuff for the apartment from the table you sent
get the list of all apartments you need to show
okay
for each apartment:
- calculate the date range between start and end, sent by the user, using
DateInterval
- for each entry in the table, remove those dates from the stored date interval
- over the remaining dates, calculate the regular price or the weekend price, depending on if it is a weekend or not
there you go: you have:
- special price, separated
- weekend price, separated
- regular price, separated
if you're worried about running the same query multiple times: don't
prepared statements are specifically made to be executed multiple times, but with different datai see u
in terms of performance, it may be the same as just reading all the data at once
i see u
or very little lower than that
wait
changing the query that much is something that wont be approved from my boss
but
calculating the special price first
well, it's that or you implement the incredibly slow solution that you have, which will only get slower and slower and slower and slower and slower ...
and then calculating the rest with the rest of dates sound like a strong path
and it is a lot easier in php too
yeah ik
and much faster
actually getting the total price first doesnt change anything
i still need to get the count of special weekend dates
nvm
yeah ill try with the php way after fetching the results
not great but better than what i have
it just seems like too much of a change for just one count of damn weekend days
$i++ <-- there, you got the countin php yes
thats not a problem
if you need it in sql, just do another query
a separate query?
yes
can't see why you need that in sql, but yes
cause i still believe theres a faster and lean way to just add a few more lines in the query that wont slow it down and get that count somehow
i got most of the stuff i need already
wait so
your code is an unreadable mess
it is absolutely horrible
agreed
there's no shame in re-writing code
i've done it a lot, others have done to my code
it's fine
nahh u didnt get me, id hapily rewrite the whole damn file
but its not something im allowed to do
i can just change/add the minimum it needs in order to work and work well
you're stuck on the .5s query then
at least not worse than it used to
my boss kinda stupid irritating and stuborn too
but thats another topic
yeah, but even an idiot can see that 0.5 seconds is too slow
he doesnt like the stored fucntion approach cause he is "not sure what it does in the background, how much it has the hit on the app"
there's 2 solution:
you gave him the one he wants
well u are right about the after query php way
knowing him thats something he would resort to if i dont come up with a better one
cause we did it before
there is no better one
there's different, but shitty
there's the rewrite in php
there's the one you gave him
yea
if you show him the sql solution is slow, then show him the php version, im sure he will pick the php one
ye
query will be faster
not always
sometimes, it's much faster to send the data to php and letting php calculate it
yeah
that was the solution for the other project i work on
also same booking stuff
but newer
more features and shit
well theres the plan
prices to php -> compare performance -> pick the faster
basically, that
thanks for the help
you're welcome
btw, you didn't explain the coalesce sprinkled everywhere
i took 2h of your time
temporary solution maybe, its still a wip feature
why don't you put
not null default '0' on the database?
it's free, no coalesce thrown randomly anymoreyeah, will remove that
the code will be a lot cleaner
i think one of them is not even needed there
its computed fields
except weekend price
the code login also has to do with the "policy" i need to follow
i cant change things that are not directly related to the thing im working on, just because i need them to work differently for my case, rather i need to make sure mine works with the current system
doesn't sound like you have policies, just a moody boss
hence the ""
that almost makes sense
well yeah
reasonable
if i do a rewrite, it needs to be done separately and through the whole project
but stuff like that are never a priority
theres always stuff "far more important" to do than going back to old code that "already works for years no need to touch it"
except when you need to implement new features and can't touch code
yeah
stuff like that can be done i mean
but only if i must
if there literally no way to avoid that
for example im adding special price to the system, i need to change the system-wide price calculations
i cant avoid that
i bet your boss wants you to do not touch anything anyway
lmao it may sound like that from my words
good guy, just a stubborn person that codes for 11 years
and is stuck to some old principles of his
yeah, stubborn usually is no different than insanity
cant argue
its usually the "own world" kind of behaviour
thats highly tied with stubbornness
good night
thx again for help and all
you're welcome, goodnigth
good luck testing everything
u are a good person
no way id end up somewhere for 2 hours random like that with some less intelligent guy looking at horrible code
dude, there's a reason why i take an holistic approach
you wanted some insane stuff that was almost implemented
yeah
you're close, you can just throw a count and stuff, and works
but slow as mollaces, as you explained
that's not a solution
its slower because of stored function
it's not just that
the 2-3% slowdown would be perfectly acceptable
it's the slow down of the function, per row
not per row
whole search query takes 0.5s
yeah, but the function runs once per row, at least
yeah
it slows down the whole query time by that much, about 10%
the total query time
but still
a slow mf
it is, but i bet you're getting too much data too
well knowing the ol code im sure theres unecessary records being fetched
rawrite is due anyway
it deserves some rewrite for the 10 years
it sounds like it needs one
i found the solution i was looking for, and it has minimal performance impact
almost none
oh, what is it?
the code is ugly tho
its a raw sql query
from 0.48s slowed down to 0.49s
so im good
cant be worse than that's in the question
well
i warned u
ai came up with it, groq 3 model
i want to commit self-defenestration
it looks like a car hit it and it is begging for mercy
you did warn me
the code is absolutely hideous
indeed
even i barely understand it
the weekend count part
but hey
it works
as long as it works ... 🤢