sql call table from subquery1 in subquery2

Question

my SQL Query:

SELECT 
    upd.*,
    usr.username AS `username`,
    usr.profile_picture AS `profile_picture`

    ,(
      SELECT
        COUNT(lik.id)
      FROM
        likes as lik
      WHERE
        upd.update_id = lik.item_id
        AND
        lik.uid = 118697835834
        AND lik.type=0
    ) as liked_update,
    (
      SELECT
        COUNT(fav.id)
      FROM
        favorites as fav
      WHERE
        upd.update_id = fav.item_id
        AND
        fav.uid = 118697835834
        AND fav.type=0
    ) as favorited_update

FROM 
    updates AS upd
    LEFT JOIN 
        users AS usr 
            ON upd.uid = usr.uid
WHERE
    upd.deleted=0
  AND
    (
      upd.uid=118697835834
      OR EXISTS
        (
          SELECT
            *
          FROM
            subscribers AS sub 
          WHERE
            upd.uid = sub.suid
              AND sub.uid = 118697835834
        )
    OR EXISTS
      (
        SELECT
          *
        FROM
          topics as topic
        WHERE
          upd.uid = topic.uid
          AND sub.uid = 118697835834
      )
    )

ORDER BY upd.date DESC
LIMIT 0, 15

i am getting an error because sub cant be called in subquery2 since its only in subquery1... how can i make sure i can call it on subquery2 as well? do i have to LEFT JOIN subscribers above on the FROM ?

Unknown column 'sub.uid' in 'where clause'

Yes, you need to join the tables. If you post your entire query, I'm betting that there is more that can be done to optimize your query and make it shorter and run faster. — Travesty3, Mar 06 '12 at 20:15
Something I don't get... if the second `exists` checks for a topic and a subscriber, isn't this redundant with the previous `exists`, which checks for just a subscriber, and wouldn't the block already be true? — Michael Fredrickson, Mar 06 '12 at 20:57
@MichaelFredrickson so you mean if the one is true the other is not being processed? — stergosz, Mar 06 '12 at 21:22
@fxuser Well, my confusion is that it looks like this to me... `if (a = 1 or (b = 1 and a = 1))`. In order for the second evaluation to be true, the first one would have already had to have been true... so why is there even the second check? — Michael Fredrickson, Mar 06 '12 at 21:28

score 2 · Answer 1 · answered Mar 06 '12 at 20:33

2

I tried to re-write your query, with keeping your original logic... but, I'm very unclear on some parts of the logic, specifically topics.

SELECT DISTINCT
    upd.*, -- Probably should not be doing * here...
    usr.username AS `username`,
    usr.profile_picture AS `profile_picture`
    COUNT(DISTINCT lik.id) AS `liked_update`,
    COUNT(DISTINCT fav.id) AS `favorited_update`
FROM 
    updates AS upd
    LEFT JOIN subscribers AS sub ON upd.uid = sub.suid
    LEFT JOIN topics as topic ON upd.uid = topic.uid
    LEFT JOIN users AS usr ON upd.uid = usr.uid
    LEFT JOIN likes AS lik 
        ON lik.item_id = upd.update_id 
        AND lik.uid = upd.uid
        AND lik.type = 0
    LEFT JOIN favorites as fav
        ON upd.update_id = fav.item_id
        AND fav.uid = upd.uid
        AND fav.type=0
WHERE
    upd.deleted = 0
    AND (
        upd.uid = 118697835834 
        OR sub.uid = 118697835834
        OR (topic.uid IS NOT NULL AND sub.uid = 118697835834)
    )
GROUP BY upd..., usr.username, usr.profile_picture
ORDER BY upd.date DESC
LIMIT 0, 15

answered Mar 06 '12 at 20:33

Michael Fredrickson

36,839
5
92
109

its better to call the columns that i only need? what if i need 90% of table columns? still increases performance? – stergosz Mar 06 '12 at 20:39
[There are several very good reasons why you shouldn't use `select *` in production queries...](http://stackoverflow.com/questions/3180375/select-vs-select-column) – Michael Fredrickson Mar 06 '12 at 20:41
on GROUP BY what did you want to write on upd... ? this results to an error.. probably you miss-typed the column there? – stergosz Mar 06 '12 at 20:44
@MichaelFredrickson: I think the `WHERE` clause can be simplified here. You are joining the tables based on `upd.uid = sub.uid` and `upd.uid = topic.uid`, so no need to check those if you are already checking `upd.uid`. – Travesty3 Mar 06 '12 at 20:46
@Travesty3 Ah, that's the tricky part... it is actually joined on `upd.uid = sub.*S*uid`... which is why the `sub.uid = 118697835834` is needed. But, you're right about topics... the check is redundant with subscribers, so I don't know why it was included in the original query. – Michael Fredrickson Mar 06 '12 at 20:52
@fxuser For `GROUP BY upd...`, you need to list out each individual column that you end up selecting. – Michael Fredrickson Mar 06 '12 at 20:54
+1 for pointing out that you shouldn't use `SELECT *` and for catching the mistakes that I missed. – Travesty3 Mar 06 '12 at 21:06
loading times are way too slow... like 27++ seconds to load... do i need to add all my required columns in group as well?? isnt this bad on performance? – stergosz Mar 06 '12 at 21:26
@fxuser It's probably all the joins that are killing the performance. If it's returning the right information, but just taking too long, we'd have to check what indexes you have, see if we can eliminate one or more of the joins... etc... – Michael Fredrickson Mar 06 '12 at 21:30

score 1 · Answer 2 · answered Mar 06 '12 at 20:16

I don't think you even need subqueries. Likely, you can do it all with joins, something similar to this (though, this is just estimation without knowing your tables):

SELECT *
FROM your_main_table upd
INNER JOIN subscribers sub
  ON upd.uid = sub.suid
INNER JOIN topics topic
  ON upd.uid = topic.uid
WHERE sub.uid = :user

Travesty3 · Answer 3 · 2012-03-06T21:04:51.567

DISCLAIMER: This isn't tested and is almost guaranteed to need some adjustment for it to do what you want, but I think it's pretty close to what you want:

SELECT 
    upd.*,
    usr.username AS username,
    usr.profile_picture AS profile_picture,
    COUNT(lik.id) AS liked_update,
    COUNT(fav.id) AS favorited_update
FROM 
    updates upd
    INNER JOIN subscribers sub ON upd.uid = sub.suid
    INNER JOIN topics topic ON upd.uid = topic.uid
    LEFT JOIN users usr ON upd.uid = usr.uid
    LEFT JOIN likes lik ON upd.update_id = lik.item_id AND upd.uid = lik.uid AND lik.type = 0
    LEFT JOIN favorites fav ON upd.update_id = fav.item_id AND upd.uid = fav.uid AND fav.type = 0
WHERE
    upd.deleted = 0
    AND (
        upd.uid = 118697835834
        OR sub.uid = 118697835834
    )
GROUP BY
    upd.*,
    usr.username,
    usr.profile_picture
ORDER BY upd.date DESC
LIMIT 15

score 0 · Answer 4 · answered Mar 06 '12 at 20:14

Those subqueries are independent subqueries and cannot share any data. You would need to do a join in your second subquery to get the result you are looking for.

Also, it's quite likely that you could do this without subqueries and only use joins, which is much faster.

sql call table from subquery1 in subquery2

4 Answers4