Query of users for some age, gender, and state, using a subselect

Question

I have this query:

SELECT * FROM
(SELECT u.id,u.email,u.verified,u.verified_on,u.created_on,ca.html AS age,cg.html AS gender,cs.html AS state
FROM users u
LEFT JOIN combo ca ON ca.combo_group='age' AND ca.value =u.age
LEFT JOIN combo cg ON cg.combo_group='gender' AND cg.value =u.gender
LEFT JOIN combo cs ON cs.combo_group='state' AND cs.value =u.state ORDER BY created_on DESC) users ORDER BY email ASC;

I am using sub query because I want sort created_on DESC and email ASC.

Would using sub query affect performance?

Answer 1

Your query doesn't do what you think it does. It discards the original ordering when it re-sorts. It might, depending on the sorting algorithm chosen, happen to come out how you want, but there's no guarantee.

I am using sub query because i want sort created_on DESC and email ASC

If that's what you want, just write:

ORDER BY created_on DESC, email ASC

Your bigger worry with performance is that combo side table, which appears to be EAV and thus likely to be painful in performance terms. Consider using a hstore or json field instead.

Answer 2

Nested query is unnecessary in this case, you can rewrite the query as,

SELECT 
    u.id
   ,u.email
   ,u.verified
   ,u.verified_on
   ,u.created_on
   ,ca.html AS age
   ,cg.html AS gender
   ,cs.html AS state
FROM users u
    LEFT JOIN combo ca ON ca.combo_group='age' AND ca.value =u.age
    LEFT JOIN combo cg ON cg.combo_group='gender' AND cg.value =u.gender
    LEFT JOIN combo cs ON cs.combo_group='state' AND cs.value =u.state 
ORDER BY u.created_on DESC, u.email ASC