如何将SELECT转换为SELECT JOIN？

I'm trying to convert my SELECT query into a faster version of SELECT including JOIN, that I found online.

Here is my code:

$stmt->prepare("SELECT * FROM books WHERE person_id = :person_id ORDER BY id DESC LIMIT 5, 10");

convert to:

SELECT  l.id, value, LENGTH(stuffing) AS len
FROM    (
        SELECT  id
        FROM    t_limit
        ORDER BY
                id
        LIMIT 150000, 10
        ) o
JOIN    t_limit l
ON      l.id = o.id
ORDER BY
        l.id

I found that code online (last piece of code). Its supposed to help with performance when doing OFFSET and LIMIT. anyways... here is what i tried:

$stmt->prepare("SELECT * FROM (SELECT id FROM books WHERE person_id = :person_id ORDER BY id DESC LIMIT 5, 10) o WHERE person_id = :person_id JOIN books l ON l.id = o.id ORDER BY l.id");

but it does not seem to work.

any help would be appreciated!

I found that code online. Its supposed to help with performance when doing OFFSET and LIMIT.

Where did you hear that? The statement you've quoted appears to be very specific to a particular database schema, and is not applicable to your situation. Forget about it.

As it stands, the SQL statement you are currently using is already optimal. If it is not performing adequately, you may need to create an index on books (person_id, id).

if anyone is wondering, I fixed it. I put the WHERE clause in the wrong place.

This is the fixed version now:

$stmt->prepare("SELECT * FROM (SELECT id FROM books WHERE person_id = :person_id ORDER BY id DESC LIMIT 5, 10) o JOIN books l ON l.id = o.id WHERE person_id = :person_id ORDER BY l.id");

Here's two articles describing the seek method:

https://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/

and

https://use-the-index-luke.com/sql/partial-results/fetch-next-page

It looks like a better fit for what you're doing. I would still venture to guess this is an indexing issue, that an explain would help with.