Speeding up SQL queries by orders of magnitude using UNION
foxhound.systems
foxhound.systems
If anything, I think many folks would say “whatever, make the two requests in parallel and zip them together in the application”. If not, WITH expressions to make the two independent queries and then a final SELECT to merge them however you prefer would be reasonable (whether that’s a join flavor or UNION/INTERSECT/etc.), but the super explosion JOIN just doesn’t cross my mind.
I think there must be a different example that they tried to anonymize for this post, otherwise they don’t need the IDs (and probably want a sum of quantity). If the author is around, what was the more realistic example behind:
“In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day. This requires a query that includes items that were both sold to customers as well as recorded as employee markouts for the specified store on the specified day.”?
What on Earth is your application where that's the case? ..A forum maybe? (Revisiting a thread looking for a particular reply I mean.)
Like, remembering the association <search term - selected results> and prominently display these?
That's the neat thing about consistent search indexing that isn't being maliciously subverted or tweaked. Once a "good enough" structure is achieved, genearally someone can quickly find their way around. I call it the Librarian effect. I first grokked it's impact when I'd volunter to reshelve books in the library. Given a relatively ordered cart of books to put back on shelves, and an arbitrary starting point in the library to begin restacking (imagine going to the bathroom and some student comes and moves your cart on you), once you've done it a few times, you can usually unconsciously navigate the entire data store.
Compare this to most Search Engines, Reddit, or most Internet forums, where you have people maliciously attacking your overall index structure in a constant conflict for top page rank, orconstantly shifting page numbers based on time elapsed since last reply/engagement sorts.
Swear to God, might one day make a Dewey Decimal System-like search Engine. I've not encountered any other way of organizing information that seems intuitively mappable quite like it. Though I'll admit, I'm not sure what gives it the "stickyness" for me, the numbers, or the physical location. I'll preferentially choose a library whose physical layout I'm familiar with over one I don't, but if you take that physical layout and switch around all the numbers, I'll find you, and beat you with the heaviest reference volume in the Library once I find it.
Seriously though, even if you throw me in an unfamiliar layout, I'll generally quickly refresh on general identifiers then do a quick jog through the Library to nail down the layout. Haven't done many non-Dewey libraries, and hate the ones set up like bookstores where you don't have interleaved levels of sorting.
LIMIT <page_size> OFFSET <page-number>*<page_size
SELECT ... where data > last_item ..
LIMIT 50; -- no OFFSET
That being said, from user POV, I usually don't like infinite scroll - it can be time consuming, as you usually can't binary / guesstimate search the result set.
That was my first reaction.
Keep in mind that high load applications have limited database connections. Running queries in parallel will be an anti-pattern in such applications.
With SQL, matching is (by far) the most computationally expensive bit of most queries. Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs.
Complicated matching criteria will force the engine to make tough trade-off decisions, which can be affected by out-of-date table statistics, lousy indexes, and a million other things, and will often force the engine to rule-out more optimized algorithms and fall back to the cruder, primitive algorithms.
[1] For most queries, "OR" is not usually a sargable operator: https://en.wikipedia.org/wiki/Sargable
[0] new term for me, and wow do I not like it
In the article, the devil is in how the second explodes in size. We are suddenly cross-joining employees with customers. Left join of stores, employees and markouts gives you more than 2 rows. It gives you all the employees assigned to the store. Regardless only 2 have markouts.
Next, you join full customers table with it. And keep on multiplying the size by further left joining. Result size might quickly exceed the memory limits for efficient joins, and the rdbms might have to resort to poor nested loops.
Or just use indexes properly; it's fairly common for modern databases to support indexes on functions of column values; using an indexed (deterministic) function in WHERE/JOIN criteria isn’t problematic. The Wikipedia article you link addresses valid concerns, but the specifics it suggests don't seem accurate for the features of modern RDBMSs.
As you note, functional indexes can sometimes be the best solution - some common examples: when working with different geospatial projections in PostGIS; when building a full-text index using `to_tsvector`. For anyone who works with relational databases that support this, definitely a good tool to have in your toolkit!
the faster query with UNION is joining 4 tables and unions with 5 tables, so the faster query is two orders of magnitude faster, because the SQL engine has to compute cartesian product on 5 instead of 7 tables
What's more important for performance of queries on larger data sets than the number of joins is that there are indexes and that the query is written in a way that can utilize indexes to avoid multiplicative slowdowns. The reason the UNION query is fast is because the query on either side effectively utilizes the indexes so that the database engine can limit the number of rows immediately, rather than filter them out after multiplying them all together. I can expand this schema to have a UNION query with two 10-table joins and it would still perform better than the 7 table query.
I think someone new to SQL is likely to read your statement and think "okay joins are slow so I guess I should avoid joins". This is not true and this belief that joins are slow leads people down the path that ends at "SQL just doesn't scale" and "let's build a complicated Redis-backed caching layer".
SQL performance is a complex topic. The point of our post was to illustrate that a UNION query can simplify how your join your tables and allow you to write constituent queries that have better performance characteristics. Morphing this into "the number of joins is smaller so the performance is better" is just incorrect.
Well written predicates and indexes can help, as well as poorly written predicates make it worse. So there is balance. This is not a shortcut or a silver bullet, it is a trade-off being made. More indexes->faster selects and slower updates/inserts. One bad index=>failed insert and possible losing customer data (happened to me once)
I agree with you that "SQL performance is a complex topic." and one should definitely study query Execution Plan to understand the bottlenecks and make optimization decisions
If the article had, instead, listed indexes, shown they were used in simple cases, shown they weren't used in the second query, dug into why they weren't (maybe they were but it was still hella slow) - that would be a ton of value!
I dare to challenge that the non-optimal query is something that a person with advanced SQL skills would do. A seasoned engineer knows that by joining two tables, you are looking at, worst case, NxM comparisons.
The problem is that you are joining the customers table with THREE other tables - the merged result of stores, employees and markouts. No matter how hard you try, you can't escape the fact that you are joining way more rows than just stores and customers.
I would call it a layman issue SQL beginners learn the hard way. Advanced SQL users know the very basics of the db they are working with.
So?
Does every blog post have to be for expert users of the system? What's wrong with a blog post that explains mistakes that a beginner might make, and a method for mitigating it.
> Arriving at query #2 to get the combined results was the intuitive way of thinking through the problem, and something that someone with intermediate or advanced SQL skills could come up with.
The issue in question is simple. It's a very good lecture for people early in their SQL journey. It would be a superb read if the author had dug into the details why it's slow.
But advanced engineers should know that LEFT JOINing 6+ tables will be huge. I disagree it's a mistake an experienced person would do.
Of course specifying useful predicates that filter down dataset and having indexes on these columns helps avoid row explosion, but you can't have an index on each and every column, especially on OLTP database.
in example #2 the author joined 7 tables (multiply row count of 7 tables and this gives you an indea of how many rows SQL engine has to churn through) - big O complexity of query is (AxBxCxDxExFxG) of course it will be SLOW.
in example #4 he joins 4 tables and unions with 5 table join, so the big O complexity of query is AxBxCxD(1+E).
same as in programming, there is Big O complexity in your SQL queries, so it helps to know O() compelxity of queries that you write
TLDR: learn the SQL Big-O and stop compaining about the query planner, pls
Perfect for a reporting database though.
But yes,
As an example, we keep our data normalized and add extra tables to do the operations we need. It’s expensive since we have to precompute, etc. But then on lookups it’s quicker.
Like everything, it depends on what you want to optimize for.
Denormalizing everything is definitely a pain; keeping data in sync in mongodb (which lacks traditional joins) on a previous project was not fun; now using postgresql.
I think a cartesian product would be:
SELECT * FROM A, B; Given sizes a, b the resulting number of rows would be a X b or O(N^2).
With a join SELECT * FROM A INNER JOIN B ON A.id = B.id then the result rows would be MAX(a,b) or O(N).
A JOIN is a predicate that filters down the dataset.
select * from A, B /* this is cartesian that slows down your query */ WHERE a.col1=B.col2
in fact both queries produce the same execution plan if you check yourself
select * from customer_order_items
select * from customer_order_items join customer_orders on order_id
select * from customer_order_items join customer_orders on order_id join customers on customer_id
..etc..
of course later on they screw it up and take the cartesian product of customer_order_items by employ_markouts, but it's just 2 big factors not 7 - their query did finish after a few seconds. usually mistakes involving cartesian products with 7 factors just run for hours and eventually throw out of memory.
my explanation to this: CPUs ave very fast. My CPU is 4Ghz so a single core can do a lot of computations in one second, and a SQL engine is smart enough to make cartesian computation (as part of a query plan) and discard the result if row does not meet predicate condition.
in fact I agree that not entire cartesian is being computed, if you specify enough predicates. But the query still multiplies rows. In the author's article he is joining employees when customer_id columns in NULL so this is technically a cartesian, because NULL predicate is not very selective (=there are a lot of rows with value of NULL)
if you study engine internals, especially MergeJoin, HashJoin, NestedLoopJoin - they all do comoute cartesian while simultaneously applying predicates. Some operations are faster because of sorting and hashing, but they still do cartesian.
Last, you have offered no significant support for your claims, and as the initiator of the discussion making the claim of cartesian joins, the burden of proof is on you to provide evidence for your claims.
In any case, your persistence in digging your hole deeper and deeper on this issue is embarrassing, and I won't be cruel enough to enable that further. Feel free to reply, but I am done, with a final recommendation that you continue your learning and not stop at the point of your current understanding. You at least seem earnest in your interests in this topic, so you should pursue it further.
it's called merge join because it employs the merge algorithm (linear time constant space)[1] - this can only be used when the inputs are sorted. likewise the hash join entails building a hash table and using it (linear time linear space)[2] but the inputs don't have to be sorted. the point of these is to avoid the O(N*M) nested loop
(Note when I say linear performance, I mean the join predicate is executed a linear amount of times, but the initial sort/hash operation was probably more like O(n log n))
this isn't really true. not every join is a cross join, and not every join even changes the grain of the result set. it is important to pay attention when your joins do change the grain of the result set, especially when they grow it by some factor - then it's more like a product as you say.
in fact both queries produce the same execution plan if you check yourself
this is true regardless of schema and statistics, it is simply how query planner works under the hood
and you can check it yourself on any database
> This latter equivalence does not hold exactly when more than two tables appear, because JOIN binds more tightly than comma. For example FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition is not the same as FROM T1, T2 INNER JOIN T3 ON condition because the condition can reference T1 in the first case but not the second.
[1] https://www.postgresql.org/docs/current/queries-table-expres...
Also:
union = distinct values
union all = no distinct
They say that query #2 is "the intuitive way of thinking through the problem, and something that someone with intermediate or advanced SQL skills could come up with" and is slow because it "eliminated the optimizer's ability to create an efficient query plan" - and they're wrong on both counts. That query is a nightmare no advanced SQL expert would write, and the plan that was generated was probably perfectly optimal to give them what they asked for. They just don't realize they asked for ridiculous nonsense that accidentally matches their expected results.
> They're using select * in a group by query with no aggregates.
The author wrote something akin to
SELECT A.*, B.x FROM A JOIN B GROUP BY A.id, b.x
This is perfectly valid SQL, AS per the SQL standard: "Each <column reference> in the <group by clause> shall unambiguously reference a column of T. A column referenced in a <group by clause> is a grouping column". From SQL-92 reference, section 7.7, http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
When using offensive language, you'd better be sure of the technical quality of what you write, if you don't want to show an ugly portrait of yourself.
See here: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.ht...
And here: https://www.percona.com/blog/2019/05/13/solve-query-failures...
no, it's not. this query only runs because postgres is not compliant with SQL-99 T301. the query is unambiguously invalid under SQL-92.
> When using offensive language
fwiw I regret my tone but this article deserves to be criticized. I love and respect rdbms technology and it's exciting to see anything "SQL" in a headline on HN - but then it bums me out to see bogus terms tossed around and insane queries presented as normal. if this were some beginner blog writing about lessons learned that's one thing, but this is a professional consultancy firm writing with the air of "you can trust us, we're experts".
SELECT
meal_items.*,
customer_id,
customers.employee_id
FROM customers
INNER JOIN customer_orders
ON customers.id = customer_orders.customer_id
INNER JOIN customer_order_items
ON customer_orders.id = customer_order_items.customer_order_id
INNER JOIN meal_items
ON customer_order_items.meal_item_id = meal_items.id
This can also be approached by adding a customer_id field to the employees table instead (in this example the no. of employees and customers are both 20000 but typically the no. of employees is less than the no. of customers so adding customer_id to employees table would be more typical - in which case the query would obviously change).
I've run into lots of cases where SQL join is clear to write, but slow, but a simple query to get ids, plus a union of fetching data for each id is fast. Again, a union of single id fetches is often faster than a single query with in (x, y, z). We can wish the sql engine could figure it out, but my experience from a few years ago is that they usually don't.
I'm not a db expert, but this seems like something that should be solvable.
I'd be delighted to learn what's going on
The best I can understand is the engines are (or were) just not setup so they can do repeated single key lookups, which is really the right strategy for a where in. As a result, they do some kind of scan, which looks at a lot more data.
To tackle this problem postgresql has a setting where you can tune the cost factor for random io.
I managed to speed up queries by about 10x by just adjusting this. One Weird Trick To Speed Up Your PostgreSQL And Put DBAs Out Of a Job!
Ah, [1] has the answer: they acknowledge that random accesses might be much slower, but expect a good share of accesses to be satisfied by the cache (that'll be a combination of various caches).
[1] https://www.postgresql.org/docs/13/runtime-config-query.html
I have actually seen it be a win to combine it like this:
SELECT ...
FROM foo
JOIN (
select 'a' as value
union all
select 'b' as value
...
) barpro SQL developers extremely rarely have a problem with query optimizer, and when they have it is extremely exotic.
it is usually ORM users that have a problem with SQL performance and blame the optimizer
That's not to say that its impossible to work around or that in a real application that you would ever be "stuck" by this. At the end of the day you deal with the software you have, but the optimizer can still have weaknesses without it totally derailing your app.
a) with everything known to the database (i.e. ids not null) the two queries produce the same result.
b) for some contents of the schema the results differ, but in the presented case some additional condition (not known to the db) holds that makes them equivalent.
Only in case b) it can be called coincidence. In case a) it’s fair to ask if the optimizer can’t do better. After all it tries to avoid cartesian products for simple joins even though that’s the text book definition.
“Not worth” to improve the optimizer is still a valid answer.
It is very highly database specific. MySQL tends to be terrible. PostgreSQL tends to be quite good. The reasons why it did not figure it out in particular cases tend to be complicated.
Except for MySQL, in which case the problem is simple. There is a tradeoff between time spent optimizing a query and time spent running it. Other databases assume that you prepare a query and then use it many times so can spend time on optimizations. MySQL has an application base of people who prepare and use queries just once. Which means that they use simple heuristics and can't afford the overhead of a complex search for a better plan.
It's quite plausible that it is distinct in the usage patterns it has chosen to optimize for, and that that design choice has been instrumental in determining what use cases it became popular for and which use cases led people to migrate off of it for a different engine, yes.
To the extent that is at play here it is somewhat of an oversimplification (even if the target audience was a factor in the sewing decision) to describe that as simple unidirectional causation from usage to design, and possibly more accurate (though still oversimplified) the other way around.
Back in the 90s and early 2000s, MySQL told people to just run queries. PHP encouraged the same.
Every other database was competing on ability to run complex queries. And so it became common knowledge that applications written for MySQL didn't port well to, say, Oracle. Exactly because of this issue.
Those applications and more like them are out there. And mostly expect to run on MySQL. So that use case has to be supported.
I was about to comment the same thing. Every engine has its quirks, and the programmer learns them over time. I went from years on MSSQL to MySQL and it was a bit rough to be generous. But now I know many of the MySQL quirks and it's fine.
A general answer, perhaps... not specific to the case you've specified.
Query optimization is a science with multiple dimensions [1]. I'd wager every problem in computer science plays a role somehow in query optimization.
Query performance is based on a combination of actions you take to optimize the design of your system to get the best performance (e.g. data modelling, index design, hardware, query style, and more), and the patterns the system can recognize based on your inputs and the data itself, with the resources it has available, to optimize your queries.
There are known patterns for optimization that are discovered over the years, many hard learned from practical experience. This is why older "popular" engines sometimes are more mature and more performant - they have optimizations built for the common use cases over long periods of time. That is not to say older engines are always better, just that they have often had more exposure to the variety of problems that occur.
The reason why the engine "can't figure it out" is that most engines, even the best ones, are quite complex - combinations of known rules as well as more fuzzy logic, where the engine uses a combination of information and heuristics to essentially explore a possible solution space, to try to find the optimal execution plan. Making the right decision, well, can be difficult and given the nature of these things, sometimes the optimizer makes the wrong decision (this is why "hints" exist, sometimes, you can force the optimizer to do what you see is obvious - but this is suboptimal for you).
In some cases, finding an optimal execution plan can actually be quite computationally expensive, and/or quite time consuming, or the engine in question may simply have no logic coded to handle the case. Optimization is all about finding the balance between finding the most performant query plan, but in the least amount of time, with the least computational and I/O impact to the overall system, that returns the right result. Optimizers are also highly depending on the capabilities of the engineering teams that build them.
It is not an easy problem, and it is an area which one could liken to almost machine learning/artificial intelligence, in one way. There are so many possible options, the problem space so big, with so many different ways to approach a given scenario, that it can be difficult for the "engine" to decide.
This is why known patterns were created, for example, dimensional data models for analytical queries vs. 3rd normal form. Dimensional data models enable certain optimizations, for example, star schemas [2]. If you take a combination of implementing known patterns, along with optimizers written by engineers that exploit those patterns, you can get to a world of better performance.
However, in a world that is, let's say.. more "open ended" - for example, the world of data in a "data lake", where data models are not optimized, data comes in unpredictable multiple shapes/sizes, then it often comes down to combinations of elegant/complex engines that can interpret the shapes of data, cardinality, and other characteristics, make use of much larger distributed compute and system performance, and in some cases - often brute force to arrive at the best query plan or performance possible.
There are so many levels of optimization.. for example, if you were to look at things like Trino [3], which started its genesis as PrestoDb in Facebook - you will see special CPU optimizations (e.g. SIMD instructions), vectorized/pipelined operations - there are storage engine optimizations, memory optimizations, etc. etc. It truly is a complex and fascinating problem.
Source: I was a technical product manager for a federated query engine.
[1] https://en.wikipedia.org/wiki/Query_optimization
Can you give an example ?
It's heavy in it's raw form, but if you add some search terms to narrow things down the DB really cuts through.
It's about 320 lines of SQL, so to make debugging a bit easier I added a dummy "identifier" column to each of the union'ed sub-queries, so first would have value 100, second 200 etc, so I could quickly identify which of the sub-queries produced a given row. Not counting in 1's allowed for easy insertion of additional sub-queries near relevant "identifiers".
When you're getting two separate sets of things in one query you should expect to use union. This isn't so much a performance optimization as just expressing the query properly.
Imagine starting with just this weird SQL that gets both employee and customer meal items using only joins, and then trying to infer the original intent.
My intuition would have been to tackle this in 3 steps with a temp table (create temp table with employee markouts, append customer meals, select ... order by). I can see the overheads of my solution but I’m wondering if i could beat the Union all approach somehow. My temp table wouldn’t benefit from an index so I’m left wondering about parallelism.
If i remember correctly though, there’s no concurrency, and therefore no opportunity for parallelism for transactions in the same session.
Also the article says these tests were done on postgres 12.6
More info in the PostgreSQL docs [1]:
> If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.
[1]: https://www.postgresql.org/docs/13/sql-createtable.html
On Mysql, using a union all creates a temp table which can perform catastrophically under database load. I’ve seen a union all query with zero rows in the second half render a database server unresponsive when the database was under high load, causing a service disruption. We ended rewriting the union all query as two database fetches and have not seen a single problem in that area since.
I was shocked by this union all behavior, but it is apparently a well known thing on MySQL.
I can’t speak to Postgres behavior for this kind of query.
Covers all the main DBMS, explained very simply from the bottom up, maintained constantly and has been read and recommended by people for years and years.
I have used [1] many times for that reason although [2] is probably more intuitive for what you want to do.
[1]
SELECT vegetable_id, SUM(price) as price, SUM(weight) as weight
FROM
(
SELECT vegetable_id, price, NULL as weight
FROM prices
UNION ALL
SELECT vegetable_id, NULL as price, weight
FROM weights
)
GROUP BY vegetable_id
SELECT vegetable_id, price, weight
FROM prices
JOIN weights
ON price.vegetable_id = weights.vegetable_idThere are also things to say about what happens if either table has duplicate vegetable_id:s. At some point it is assumed that you have processed it in such a way that the table is well formed for the purpose.
I assume somewhere there is a similar assumption in TFA.
SELECT meal_items.*, employee_markouts.employee_id, customer_orders.customer_id
FROM meal_items
LEFT JOIN employee_markouts ON employee_markouts.meal_item_id = meal_items.id
LEFT JOIN employees ON employees.id = employee_markouts.employee_id
LEFT JOIN customer_order_items ON customer_order_items.meal_item_id = meal_items.id
LEFT JOIN customer_orders ON customer_orders.id = customer_order_items.customer_order_id
LEFT JOIN customers ON customers.id = customer_orders.customer_id
WHERE (employees.store_id = 250 AND
employee_markouts.created >= '2021-02-03' AND
employee_markouts.created < '2021-02-04') OR
(customers.store_id = 250 AND
customer_orders.created >= '2021-02-03' AND
customer_orders.created < '2021-02-04') id |label |price|employee_id|customer_id|
---|------------|-----|-----------|-----------|
...
344|Poke | 4.18| 3772| 13204|
344|Poke | 4.18| 3313| 13204|
344|Poke | 4.18| 2320| 13204|
344|Poke | 4.18| 632| 13204|
344|Poke | 4.18| 4264| 13204|
344|Poke | 4.18| 699| 13204|
344|Poke | 4.18| 1070| 13204|
344|Poke | 4.18| 3022| 13204|
344|Poke | 4.18| 1501| 13204|
344|Poke | 4.18| 808| 13204|
344|Poke | 4.18| 2793| 13204|
344|Poke | 4.18| 1660| 13204|
344|Poke | 4.18| 932| 13204|
...
Right you are! :) Should have given it more thought before posting. Thanks for jumping in.
select mi.*, x.employee_id, x.customer_id
from meal_items mi
join lateral (
select e.store_id, em.created, em.employee_id, customer_id = null
from employee_markouts em
join employees e on e.id = em.employee_id
where em.meal_item_id = mi.id
union all
select c.store_id, co.created, employee_id = null, co.customer_id
from customer_order_items coi
join customer_orders co on co.id = coi.customer_order_id
join customers c on c.id = co.customer_id
where coi.meal_item_id = mi.id) x
where x.store_id = 250 and x.created between '2021-02-03' and '2021-02-03'
order by mi.idSQL is very powerful and really not so complicated. Schema, index, and constraint design can be quite challenging, depending on the needs, but that's true whether using SQL or an ORM.
The problem I see often is that people use ORMs and never learn SQL. Also often, one has to really work to understand how their ORM is handling certain situation to avoid problems like N+1 or cartesian products.
In the end, it seems that rather than learning SQL, the developer ends up learning just as much or more about a specific ORM to get the same quality/performing results they would have gotten by using direct SQL.
Eg instead of this:
ON (customer_order_items.meal_item_id = meal_items.id
OR employee_markouts.meal_item_id = meal_items.id
)
ON meal_items.id = COALESCE( employee_markouts.meal_item_id, customer_order_items.meal_item_id )
The problem is the more optimizations you implement the more possibilities the query planner has to evalue which may result in (1) a too long query planning phase or (2) the possibility of de-optimizing a good query because an optimization was falsely predicted to be faster
Additionally, in this example, the rewritten query is easier to read and maintain.
This isn't true if you have certain use cases in your application, pagination being one of them. There's no simple way to implement pagination when you have two or more queries that return and unknown number of results and you're tasked with maintaining a consistent order across page changes.
If you make a single query do all the work, it's easy to implement paging in any number of ways, the most performant being to filter by the last id the client saw. If your users aren't likely to paginate too far into the data, LIMIT and OFFSET will work fine as well.
>In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day
That's one thing. The end user doesn't see these as two separate requests just because that's how it's modeled in the database.
I don't mean to denigrate this article, just wonder if others have the same reaction.