2
\$\begingroup\$

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?

\$\endgroup\$
  • 5
    \$\begingroup\$ Cross-posted from stackoverflow.com/q/20492709/398670. Please do NOT duplicate your questions across multiple sites; if you really feel you must, link between them to avoid wasting everybody's time. \$\endgroup\$ – Craig Ringer Dec 10 '13 at 12:23
  • \$\begingroup\$ yes my friend have posted there because i was not getting response from here and i banned from asking question in SO \$\endgroup\$ – user2907171 Dec 10 '13 at 12:29
  • 5
    \$\begingroup\$ @user20907171 Guess why you're banned on Stack Overflow? If you copy and paste posts, you'll get banned here pretty quickly too. Consider changing whatever behavior is getting you banned instead. \$\endgroup\$ – Craig Ringer Dec 10 '13 at 12:33
5
\$\begingroup\$

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.

\$\endgroup\$
4
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.