Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Fast select count

    Hi guys .. i have a trouble i want to select count(id) FROM 5 tables and choose the quicker way...

    I tried something like


    SELECT
    (SELECT (COUNT(id)) AS likes FROM status_likes WHERE nick='Trki') as likes_count,
    (SELECT (COUNT(id)) AS comments FROM comments WHERE nick='Trki') as comments_count,
    (SELECT (COUNT(id)) AS statuses FROM statuses WHERE nick='Trki') as statuses_count


    which works good but isn't it as slow as i would make 5mysql queries separetly with SELECT ?

    OR is there some better way? :P

    THX!


    +++ ANOTHER PROBLEM

    This query SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0 returns this:

    shared_from_id
    3481
    3455
    3423
    3388
    3386
    3362
    3272
    3117

    What is good.. and why then i cant use the all query like this ? :

    SELECT nick FROM statuses WHERE id IN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0)

    The program died or show the result with duration more then 31s :/


    IF someone would help i would be glad
    Last edited by Trki; 11-15-2012 at 02:33 PM.

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    What is good.. and why then i cant use the all query like this ? :

    SELECT nick FROM statuses WHERE id IN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0)

    The program died or show the result with duration more then 31s :/
    Using "WHERE id IN" in this way is going to kill you. Just join the table with itself.

    Code:
    SELECT s1.nick
    FROM statuses as s1
    JOIN statuses as s2
    ON s1.id = s2.shared_from_id
    WHERE s2.shared_from_id <> 0
    This can give you multiple rows for every ID though... I guess you don't want that. Should be simple enough to handle with code after the query runs. If that's no good then replace the 2nd table reference with a temp table using your SELECT DISTINCT query like this:

    Code:
    SELECT s1.nick
    FROM statuses as s1
    JOIN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0) as s2
    ON s1.id = s2.shared_from_id
    WHERE s2.shared_from_id <> 0
    Not sure what performance will be like on that query though.

    Might be a good idea to add an index on shared_from_id.

    p.s. You don't even need the "WHERE" clause, unless you want to exclude the row where id = 0. But it shouldn't matter too much.

  • #3
    Regular Coder
    Join Date
    Jan 2012
    Location
    Slovakia
    Posts
    111
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    Using "WHERE id IN" in this way is going to kill you. Just join the table with itself.

    Code:
    SELECT s1.nick
    FROM statuses as s1
    JOIN statuses as s2
    ON s1.id = s2.shared_from_id
    WHERE s2.shared_from_id <> 0
    This can give you multiple rows for every ID though... I guess you don't want that. Should be simple enough to handle with code after the query runs. If that's no good then replace the 2nd table reference with a temp table using your SELECT DISTINCT query like this:

    Code:
    SELECT s1.nick
    FROM statuses as s1
    JOIN (SELECT DISTINCT shared_from_id FROM statuses WHERE shared_from_id<>0) as s2
    ON s1.id = s2.shared_from_id
    WHERE s2.shared_from_id <> 0
    Not sure what performance will be like on that query though.

    Might be a good idea to add an index on shared_from_id.

    p.s. You don't even need the "WHERE" clause, unless you want to exclude the row where id = 0. But it shouldn't matter too much.
    HM.. your code works gr8 really nice :P But i dont need just this: (from your code which are the authors of statuses ... )

    nick;id
    Paulis1992;3362
    george12;3272
    jdawg91;3388
    Horin;3386
    Horin;3386
    Horin;3386
    Horin;3386
    Horin;3386
    Horin;3386
    Horin;3386
    domin47;3423
    masuronike;3117
    masuronike;3481
    george12;3455

    but also this:

    nick;shared_from_id
    george12;3362
    Gianniluke;3272
    rdy2win;3388
    martinaltman;3386
    Trki;3386
    masuronike;3386
    C1zk0;3386
    pLAYERsvk;3386
    JoHnnn;3386
    MACHi;3386
    Out92;3423
    czechpete;3117
    Strupik;3481
    Strupik;3455

    which is SELECT nick,shared_from_id FROM statuses WHERE shared_from_id<>0

    ... the people who shared...ideas ? :P


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •