Question 21876 in Mysql Question posted in
The official MySQL documentation can be found here.

Apply LIMIT And OFFSET Clauses In A MySQL Query To A Specific Table, When The Query Selects Data From Multiple Tables

I’m in the early stages of setting some functionality to control pagination of some image board components with PHP/MySQL.

What I need to do initially is set the LIMIT and OFFSET clauses only on the boards table part of the query. This is so I can paginate by boards displayed.

The images that appear on the boards (4 preview images per board) are controlled by a counter inside a while loop when this data is outputted, so it is important that LIMIT or OFFSET are not applied to those.

The data is fetched with one query to prevent doing nested MySQL calls to the database which would be a performance problem.

There are 3 tables at play in the MySQL below – a boards table, an images table, and a boards_images table that is a pivot/linking table with a many-to-many relationship that stores the images that are allocated to the boards.

Question

In the following code, how would I set it so the LIMIT and OFFSET clauses only apply to the boards table. I can’t work out if I need to group the data, or do a subquery, neither of which I have done before, or if the solution is neither of those approaches?

Initial Query

    $s = "SELECT boards.board_name, boards.board_id, boards.user_id, images.filename, images.image_title
    FROM boards
    LEFT JOIN boards_images ON boards_images.board_id = boards.board_id
    LEFT JOIN images        ON boards_images.image_id = images.image_id
    WHERE boards.user_id = :user_id
    ORDER BY boards.board_id DESC
    LIMIT 20 OFFSET 20 // only apply this to the boards table
    ";

// then add the $queryString variable to a PHP PDO prepare() method etc

Attempting It With A Subquery

In relation to Rick James answer, I think this is more what I need to be aiming for, it still isn’t working though and throws and error on the first table to be queried in the first SELECT clause.

Within this I have set up a subquery, which contains the LIMIT and OFFSET

SELECT b.board_name, b.board_id, b.user_id, i.filename, i.image_title
FROM (
    SELECT board_name, board_id, user_id
    FROM boards AS b
    WHERE b.user_id = :user_id
    ORDER BY b.board_id DESC
    LIMIT 10 OFFSET 10
) AS t
LEFT JOIN boards_images AS bi ON bi.board_id = t.board_id
LEFT JOIN images AS i ON bi.image_id = i.image_id
Tags:

4

Answers


  1. Chosen as BEST ANSWER
    0

    I managed to find the solution to this, which was as follows:

    SELECT b.board_name, b.board_id, b.user_id, i.filename, i.image_title
    FROM (
        SELECT user_id, board_id, board_name
        FROM boards
        WHERE user_id = :user_id
        ORDER BY board_id DESC
        LIMIT :limit OFFSET :offset
    ) AS b
LEFT JOIN boards_images AS bi ON bi.board_id = b.board_id
LEFT JOIN images AS i ON bi.image_id = i.image_id

    This only places the LIMIT and OFFSET on the boards table as desired. The numeric values for :limit and :offset are obviously defined outside of the MySQL for security reasons


  2. 0 
    SELECT ...
    FROM ( SELECT ... ORDER BY ... LIMIT .. OFFSET .. ) AS t1
    JOIN ... ON ...
    ORDER BY ...

    In some cases, I recommend this as an optimization for a slow query. By limiting (via LIMIT or GROUP BY or DISTINCT) the number of rows in the ‘derived’ table (t1), the rest of the query runs faster. Also it may be possible to make better use of a composite index in t1.

    I may even suggest another optimization that uses the above technique. JOIN back to the original table to pick up the bulky columns (eg TEXTs) rather than hauling them around for filtering out later (via LIMIT, etc).

    OFFSET is a terrible way to do pagination. In the simple formulations, the SQL gathers everything first, then sorts, then does offset+limit. Or, if an INDEX can help with the ORDER BY + LIMIT, the query runs slower and slower as you page forward. More on Pagination .

    The formulation above tries to isolate the pain of OFFSET in the subquery. However, without seeing the entire query, plus SHOW CREATE TABLE, I can’t say how effective the technique is.

    There is a possible problem is with the JOIN. If each row in t1 ‘joins’ to zero or more than one row in the other table(s), then the LIMIT cannot be used in the derived table — it will grab too many or too few rows. Again, I need to see the use case before saying whether your query has this problem.

    LEFT JOIN t2... allows you to include missing rows from t2. You get NULL for any of t2.*. This may solve the "zero" case above.

    ( SELECT GROUP_CONCAT(name) FROM pets ... ) AS pet_names could be used to handle the "or more" case above. That way, you have a commalist of the pet names in a single row. It also handles the "zero" case.

    SELECT  kids.child_name,
        ( SELECT GROUP_CONCAT(name) FROM pets 
            WHERE kids.id = pets.owner_id ) AS pet_names
    FROM kids
    ORDER BY ...
    LIMIT ... OFFSET ...

    Now you can further optimize things by getting rid of OFFSET!
    Instead, "remember where you left off" in kids (based on the ORDER BY). That is discussed further in my pagination link above.

    Login or Signup to reply.
  3. 0

    EDIT: The OP declares his question very clearly: "In the following code, how would I set it so the LIMIT and OFFSET clauses only apply to the boards table." I am interested that Rick James’ answer, and the discussion it provoked, focus on performance and the general question of pagination. Can someone tell me if this divergence is because the OP requested a Canonical answer?

    ORIGINAL ANSWER:
    Here’s a simple approach that delivers the required data, albeit in a different structure to the OP’s spec. It just requires you to parse the images column values for the individual images in each board:

    SELECT b.board_name,b.board_id, b.user_id, group_concat(concat(i.filename,"||", i.image_title) SEPARATOR ",") as images
FROM boards AS b
LEFT JOIN boards_images AS bi ON bi.board_id = b.board_id
LEFT JOIN images AS i ON bi.image_id = i.image_id
WHERE b.user_id = :user_id
GROUP BY  b.board_id,b.board_name,b.user_id
ORDER BY b.board_id DESC
LIMIT 10 OFFSET 10
    Login or Signup to reply.
  4. 0

    Please use this sql in your project.

    SELECT customOffers.OfferNum, items.colour, items.availability
FROM 
(SELECT OfferNum
    FROM offers
ORDER BY id ASC 
    LIMIT 0 , 3) as customOffers
    JOIN items ON items.OfferNum = offers.OfferNum
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search