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 12 of 12
  1. #1
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts

    Combine 3 queries in to 1

    I'm trying to combine 3 MySQL queries in to one.

    This is what I am using to grab my news entries (with pagination) :

    Code:
    SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url FROM news 
    A INNER JOIN (SELECT sid FROM news WHERE approved=1 ORDER BY sid desc LIMIT $start, $limit)
    B USING (sid)
    Now, I've recently added a comments feature and would like to get the total amount of comments for each "sid"

    The part that gets confusing for me is that I need to match "sid" (from news.news) with "page_id" (from comments.pages) to grab it's unique "id".

    Code:
        +----+---------+
        | id | page_id |
        +----+---------+
        |  1 |      87 |
        |  2 |      86 |
        | 41 |      85 |
        |  3 |      84 |
        | 13 |      83 |
        +----+---------+
    Now with that unique "id", I need to query "comments.comments" and match it with "page_id" column and count(*) how many comments it has - WHERE is_approved = 1

    Code:
        SELECT page_id,is_approved,count(*) FROM comments WHERE page_id = $id and is_approved = 1;
        +---------+-------------+----------+
        | page_id | is_approved | count(*) |
        +---------+-------------+----------+
        |       1 |           1 |        2 |
        +---------+-------------+----------+
    is this possible?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Explain to me first what possible reason there is to do this:
    Code:
    SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url FROM news 
    A INNER JOIN (SELECT sid FROM news WHERE approved=1 ORDER BY sid desc LIMIT $start, $limit)
    B USING (sid)
    Instead of simply doing
    Code:
    SELECT sid, title, time, bodytext, author, url 
    FROM news 
    WHERE approved=1 
    ORDER BY sid desc LIMIT $start, $limit
    ????
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Assuming you can't come up with any reason for doing it the way you did, then the answer is easy:
    Code:
    SELECT N.sid, N.title, N.time, N.bodytext, N.author, N.url, COUNT(C.page_id) AS commentCount
    FROM news AS N LEFT JOIN comments AS C
    ON N.sid = C.page_id AND C.is_approved = 1
    WHERE N.approved=1 
    GROUP BY N.sid, N.title, N.time, N.bodytext, N.author, N.url
    ORDER BY N.sid DESC
    LIMIT $start, $limit
    Beats me why you would name a field sid in one table and the corresponding field page_id in another table, but if that's what you did, then that's what the query looks like.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    Or are you saying there is a third table involved???

    I don't understand this:
    I need to match "sid" (from news.news) with "page_id" (from comments.pages)
    I don't see you using any table named comments.pages anywhere in there. Your only query that mentions "comments" at all just does SELECT ... FROM comments
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Sorry, I was just doing those queries to show you how the data was stored. The comment script was made by someone else - I'm just trying to incorporate in to my news script.

    Code:
    SELECT A.sid, A.title, A.time, A.bodytext, A.author, A.url FROM news 
    A INNER JOIN (SELECT sid FROM news WHERE approved=1 ORDER BY sid desc LIMIT $start, $limit)
    B USING (sid)
    I was told this above code would be more efficient once I had a lot of news entries.

    My news script gets it's information from the 'news' table in the 'news' database.

    I need to match my "sid" column in the 'news' table with "page_id" (from the 'comments' database, 'pages' table) to grab it's unique "id".

    Once I have the 'id', I need to query 'page_id' (from the 'comments' database, 'comments' table) and count(*) the results - where is_approved = 1

    I hope that clears things up. I realize it's a stupid setup, but I didn't design it that way. I was just hoping I could work with it.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    I was told this above code would be more efficient once I had a lot of news entries.
    Yes, if (a) SID was *NOT* your primary key and (b) if "lot of news entries" means you have millions of them (or at least hundreds of thousands).

    As it is, you'll never see the performance difference, if there even is one. If anything, I'd guess your version could be slower.

    So you *DO* have three tables.

    No problem. Just slip that other table into the single query:
    Code:
    SELECT N.sid, N.title, N.time, N.bodytext, N.author, N.url, COUNT(C.page_id) AS commentCount
    FROM news.news AS N 
    LEFT JOIN comments.pages AS P ON N.sid = P.page_id
    LEFT JOIN comments.comments AS C ON P.id = C.page_id AND C.is_approved = 1
    WHERE N.approved=1 
    GROUP BY N.sid, N.title, N.time, N.bodytext, N.author, N.url
    ORDER BY N.sid DESC
    LIMIT $start, $limit
    That looks really strange to me.
    You are saying that comment.pages.page_id is the FOREIGN KEY to match up with news.news.sid?
    And yet comment.pages.id matches up with comments.comments.page_id?
    Why would you not name matching columns in the different tables the same???
    Ah, well...

    I would like to comment that it's not a really good idea to have the comments in a separate database from the news. You will suffer a performance penalty for doing so. Again, unless you have hundreds of thousands of records you'll probably never see the difference, so it likely doesn't matter.
    Last edited by Old Pedant; 04-25-2013 at 09:02 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    In other words, I would have had:
    Code:
    CREATE TABLE news (
        newsid INT AUTO_INCREMENT PRIMARY KEY,
        ...
    );
    
    CREATE TABLE pages (
        pageid INT AUTO_INCREMENT PRIMARY KEY,
        newsid INT,
        CONSTRAINT FOREIGN KEY newsid REFERENCES news(newsid),
        ...
    );
    
    CREATE TABLE comments (
        pageid INT,
        CONSTRAINT FOREIGN KEY pageid REFERENCES pages(pageid),
        ...
    );
    Or similar. So that the matching fields in multiple tables have the same name. *MUCH* less confusing.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Thank you, it works perfectly.

    Why would you not name matching columns in the different tables the same???
    This is how the comment script was designed (not by me). It's crazy, I know - but the script has so many features, the DB structure appears to be it's only downfall.

    would like to comment that it's not a really good idea to have the comments in a separate database from the news. You will suffer a performance penalty for doing so.
    Would you recommend I move it to the 'news' database? How much of a performance penalty would it be?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    I wouldn't move it unless you indeed have at least tens of thousands of records. You'll never see the performance difference for relatively small tables. (Reason: MySQL *tries* to keep as much data as it can in memory. When you use multiple databases, that means it has to allocate separate buffer areas for each database. Whereas when you use a single database, it can more efficiently keep reusing the single buffer area for that database. But I have, for example, a case where I *had* to use multiple databases [because they were created separately and are uploaded and updated by separate mechanisms]. And one of them has a single table with 14 million records. And yet even so my queries using the two databases are *reasonably* efficient. I'd probably gain maybe 25% performance if they were in one DB, but the effort to do that with all the other considerations just doesn't make it worth while.)

    In other words: Don't sweat the small stuff until you find you really need to.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Ok will do - thanks again for all your help.

  • #11
    New Coder
    Join Date
    Jan 2010
    Posts
    68
    Thanks
    3
    Thanked 5 Times in 4 Posts
    Actually one last thing (if you don't mind) ... any recommendations on how to index this?

    I'm currently testing this on my new site and here is the EXPLAIN :



    I've never dealt with anything cross database before. The "news.approved" is indexed.

  • #12
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,688
    Thanks
    80
    Thanked 4,650 Times in 4,612 Posts
    LOL! Quite frankly, who cares? With only 85 rows being scanned, MySQL will do it all in memory anyway.

    But it looks to me like you don't have a PRIMARY KEY on the NEWS table, and that's a mistake, for sure. (If you have one, MySQL has decided not to bother using it, because of finding only 85 eligible rows, which makes sense.)

    And then on the pages table you could probably use two indexes: One for the column (page_id?) that references the news table and one for the column (id?) that references the comments table.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

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