0

I think my query is not optimized. Can you see any way to ameliorate ?

SELECT q_id, q_question, q_userID, q_targetID, q_type,
    (SELECT concat(u_firstname, ' ',u_lastname) from user where u_id = q_userID), 
    (SELECT count(a_id) FROM answer where a_questionId = q_id),
    (SELECT count(a_id) FROM answer where a_questionId = q_id AND a_response = 1)
FROM question INNER JOIN friend ON
    (CASE 
        WHEN q_type = 1 THEN 
            (f_friend_one = q_userID AND f_friend_two = ?) OR
            (f_friend_two = q_userID AND f_friend_one = ?) OR
            (f_friend_one = q_targetID AND f_friend_two = ?) OR
            (f_friend_two = q_targetID AND f_friend_one = ?)
        WHEN q_type = 2 THEN (1 = ( SELECT count(g_id) FROM `group` where g_userID = ? and g_questionID = q_id))
    END)
WHERE f_type = 1 AND (0 = ( SELECT count(a_id) FROM answer where a_userId = ? and a_questionId = q_id)) AND q_archived = 0
Okn
  • 698
  • 10
  • 21
  • 1
    This question requires a schema listing, and in addition a donation to a charitable foundation of $5 – Drew Nov 05 '15 at 23:36
  • 1
    Do you realize that the main optimization tool is the database EXPLAIN command - Which we don't have access to? Because YOU are the one with the database, not us. See if this helps: http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/ – Turophile Nov 05 '15 at 23:42
  • 1
    @Drew - I thought it was "buy me a beer" but I guess charities are OK too. – Turophile Nov 05 '15 at 23:44
  • 1
    it was a joke Miguerto, not a great one. But yes @Turophile, I might just go to the store with that thought – Drew Nov 05 '15 at 23:46
  • 1
    Check [this one](http://stackoverflow.com/a/13368211) out of mine @Miguerto, but only the General Comments and Sharing sections. We will be glad to help. – Drew Nov 05 '15 at 23:48
  • 1
    In addition to what's been said, while it's right to use prepared statements in production, I think it's easier to review queries in development if you temporarily dispense with prepared statements – Strawberry Nov 06 '15 at 00:49

0 Answers0