Skip to content
  • Dries Buytaert's avatar
    · 8a7690af
    Dries Buytaert authored
    Wheep! Wheep! Major performance optimization for index.php: we reduced the
    number of SQL on the mainpage with factor 10 and shortened the code with 4
    lines.  There is only *one* optimized SQL query left, being:
    
    $result = db_query("SELECT stories.*, COUNT(comments.sid) AS comments FROM
    stories LEFT JOIN comments ON stories.sid = comments.sid WHERE
    stories.status = 1 AND stories.time <= $date GROUP BY stories.sid ORDER BY
    stories.sid DESC LIMIT $number");
    
     Note: we use a LEFT JOIN to retrieve the number of comments associated
           with each story.  By retrieving this data now, we elimate a *lot*
           of individual queries that would otherwise be required inside the
           while-loop.  If there is no matching record for the right table in
           the ON-part of the LEFT JOIN, a row with all columns set to NULL
           is used for the right table.  This is required, as not every story
           has a counterpart in the comments table (at a given time).
    8a7690af