Comment by samwillis | Hacker News Reader

Comment by samwillis | Hacker News Reader

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/#...

MySQL 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-restriction...

Ah 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.

Yeah 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.

It’s because it is using JOIN which isn’t one of the problematic operations

You can use LIMIT in correlated subqueries in MySQL +8 using LATERAL

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-p...

Might 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
    )

Mmm 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.

I 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

This 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.

Ohhh nice catch!

Won'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.

Reply on news.ycombinator.com