MariaDB - breaking up a multi join into CTEs So I have the following query:

SELECT 
    i.mongodb_id
FROM
    display_list_lists dll
JOIN
    display_lists dl
    ON
        dll.display_list_id = dl._id
JOIN
    list_items li
    ON
    dll.list_id = li.list_id
JOIN
    items i
    ON
    li.item_id = i._id
WHERE
    dl.mongodb_id = ?
    AND
    dl.account_id = ?


since, I only need the display_lists._id in order to then find the other details, would it be better to somehow use CTEs instead?

Broken up, it would look like this:

SELECT
    _id
FROM
    display_lists dl
WHERE
    dl.mongodb_id = ?
    AND
    dl.account_id = ?;



SELECT
    *
FROM
    display_list_lists dll
WHERE
    dll.display_list_id = ?; // previous _id



SELECT
    *
FROM
    list_items li
WHERE
    li.list_id IN (?); // use list ids from previous results



SELECT
    *
FROM
    items i
WHERE
    i._id IN (?); // use item ids from previous results
Your 'join' version is plenty good.  For performance, you need these indexes (or PKs):

dl:  (mongodb_id, account_id,  -- in either order
      id)                      -- third
dll:  (display_list_id, list_id)  -- in this order
li:  (list_id)
i:  (id, mongodb_id)   -- unless (id) is the PRIMARY KEY


Index Cookbook