SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select from 4 categories using LIMIT

    Hello

    Suppose I have a forum app, like vbulletin. I would store the threads in THREADS table and THREADS table will have forum_id field that would tie it with the FORUMS table.

    Now I want to display 4 blocks on home page, each block is the latest 10 posts from 4 different forums.

    I also want to select forum titles, so I can use them in block titles. the forum titles are store in FORUMS table, of cause.

    Is there a way to select the 40 rows from THREADS table in one SQL select for that?
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The UNION of 4 queries, which each join the threads and forums tables, ordered by date with a limit of 10.

  3. #3
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. It does look like UNION is the best way to go. I wonder if its fast. I mean, as far as performance is concerned, is this faster than doing 4 individual selects separately?
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    It will take about the same time on the database, but you won't have four round-trips between your web server and database server, so the communication overhead is reduced significantly.


Bookmarks

Posting Permissions

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