3 ms·
It looks like “fast_page” is performing two completely separate queries, the first just retuning the id, the second retuning the full row for each id. Then doin
by samwillis 4y ago
It looks like “fast_page” is performing two completely separate queries, the first just retuning the id, the second retuning the full row for each id. Then doing the join with Ruby, having to do two full round trips to the db.
The referenced “Deferred join technique” however does a single query using an inner join to achieve the same result.
So, if I’m reading this right, “fast_page” could be made even faster by doing an inner join instead, removing the additional query. I’m not sure why they wouldn’t have done that? It would also ensure both queries were run in the same transaction so you don’t experience race conditions. I’m not a rail dev so maybe that’s a given within a request/response process.
As someone else said, I’m surprised the query planner doesn’t account for this already.
These “deferred joins” where you join in your application code are useful for n+1 type issues. The Django orm has a .prefetch_related method [0] enabling you to optimise down to a single additional query where you would have had a secondary query for each row.
0: https://docs.djangoproject.com/en/4.1/ref/models/querysets/#prefetch-related https://docs.djangoproject.com/en/4.1/ref/models/querysets/#...
- aarondf 4y agoMySQL often doesn't support limit in subqueries, hence the roundtrip. > ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' See https://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html https://dev.mysql.com/doc/refman/5.7/en/subquery-restriction...
- samwillis 4y agoAh ok, confusingly the OP (and your blog post they link to) both shows example SQL with a subquery using limit and imply it’s from a book on MySQL optimisations.
- aarondf 4y agoYeah that's a bit confusing. The deferred join in the book does indeed use an inner join but my package (and PlanetScale's) both use a "deconstructed subquery." I made that choice for mine because I didn't want to pollute the `select` part of the developer's query to exclude the joined data. If we were writing the whole query from scratch we could, but not in the scenario where the package is actually used, unfortunately.
- EGreg 4y agoIt’s because it is using JOIN which isn’t one of the problematic operations
- gavinray 4y agoYou can use LIMIT in correlated subqueries in MySQL +8 using LATERAL https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tabl... Prior to this you can emulate it with ROW NUMBER Window function in subquery, and this is what LATERAL gets turned into in many DB engines I believe https://stackoverflow.com/questions/73127487/aws-athena-v2-prestodb-reference-left-hand-row-in-right-hand-subquery https://stackoverflow.com/questions/73127487/aws-athena-v2-p...
- simonw 4y agoMight be possible to use a CTE instead, something like this: with page_numbers as ( select id from docs order by id limit 25 offset 50 ) select * from docs where id in ( select id from page_numbers )
- aarondf 4y agoMmm yeah, that's clever! I like it. I think for my package (the Laravel one) I'll have to hold off on that because CTEs only work with MySQL 8.0 and up. Also the Laravel query builder doesn't have methods to add CTEs, unfortunately. I'm gonna noodle on this though, there's something nice about it.
- aledalgrande 4y agoI don't know if it's the same for MySQL, but in Postgres a CTE would possibly force the planner to make less efficient choices [1]. Would be interesting if someone who knows more than me could clarify on this [1] https://hakibenita.com/be-careful-with-cte-in-postgre-sql https://hakibenita.com/be-careful-with-cte-in-postgre-sql
- Lukas_Skywalker 4y agoThis changed in version 12. See the disclaimer: This article is intended for PostgreSQL versions 11 and prior. Starting at version 12, PostgreSQL changed the way it treats CTE to prevent the issues described in this article.
- aledalgrande 4y agoOhhh nice catch!
- deleted 4y ago[deleted]
- flukus 4y agoWon't this be flat out wrong when something you're joining to has a condition in the where clause? Something like this: select * from Car inner join Color on Car.ColorId = Color.Id where Color.Name = 'Red' You can't get the correct paginated list of car id's without doing the join in the first query. Even more wrong if you're ordering off the joined table.