is it possible to reduce duplicated sub query lookups?

Question

I am trying to run this as a single query, but with the duplicated sub queries only being executed once, I thought it may be possible with a JOIN clause but I am not sure if it is feasible as Im looking up the name and role field from the same row 3 times? The only way I can think to do it is to execute the sub query once from my code to get the role and name values to inject into this main query.

SELECT id, isActive, role
FROM accountMembers
WHERE account=:acc
AND isActive=true
AND (
        (
            name > (SELECT name FROM accountMembers WHERE account=:acc AND id =:id)
            AND role = (SELECT role FROM accountMembers WHERE account=:acc AND id =:id)
        )
        OR role > (SELECT role FROM accountMembers WHERE account=:acc AND id =:id)
)
ORDER BY role ASC, name ASC LIMIT :lim

got to be honest looking at this statement I am a little lost of what you are actually trying to accomplish. What is the actual desired result? your question could benefit from adding some example data and expected result. Also you should probably show or discuss how/when :acc and :id are set — Matt, Nov 12 '17 at 23:11
@Matt I added the order by clause, I am paginating the results based on the last received `accountMember`, and the list is ordered by role, and then `name` — Daniel Robinson, Nov 12 '17 at 23:45
https://stackoverflow.com/questions/3799193/mysql-data-best-way-to-implement-paging okay but seems like you should be using `LIMIT` and `OFFSET` and specifying the order by. Without know your data set it seems like using name alphabetical order greater than name could give you odd results but at last I don't know because that is all speculation without seeing example data. good luck — Matt, Nov 13 '17 at 17:41

score 1 · Answer 1 · answered Nov 12 '17 at 23:09

MySQL supports tuple comparisons:

SELECT am.*
FROM accountMembers am
WHERE am.account = :acc and
      am.isActive = true and
      (am.role, am.name) > (select am2.role, am2.name
                            from accountMembers am2
                            where am2.account = :acc AND am2.id =:id
                           );

Even without that, you can move the logic to the from clause:

SELECT am.*
FROM accountMembers am JOIN
     ON am2.account = :acc AND am2.id =:id

WHERE am.account = :acc and
      am.isActive = true and
      (am.role > am2.role or
       am.role = am2.role and am.name > am2.name
      );

score 0 · Accepted Answer · answered Nov 22 '17 at 20:26

the answer is to just include the table in the from claus twice with a second alias to distinguish between the two and then include the additional constraints as follows:

SELECT a1.id, a1.isActive, a1.role
FROM accountMembers a1, accountMembers a2
WHERE a1.account=:acc
AND a1.isActive=true
AND a2.account=:acc
AND a2.id=:id
AND (
        (
            a1.name > a2.name
            AND a1.role = a2.role
        )
        OR a1.role > a2.role
)
ORDER BY a1.role ASC, a1.name ASC LIMIT :lim

is it possible to reduce duplicated sub query lookups?

2 Answers2