SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inefficient PHP Code?

    Hi everyone

    On my front page, I have it pull the last unique forum posts. For some reason, it takes a moment to load this section of the page so I think it may be inefficient due to the size of my forums. I don't want to prune my forums just yet because they're really not all that large.

    So my question is, is there a way to speed up this query?

    Code:
    $result=mysql_query("SELECT a.username
         , (CASE WHEN (b.subject = '') THEN (c.subject) ELSE (b.subject) END) AS subject
         , c.tid
         , b.pid
         , DATE_FORMAT(FROM_UNIXTIME(b.postdate), '%M %e %Y ') AS postdate
      FROM deluxebb_posts b
    INNER JOIN deluxebb_users a ON b.author = a.uid
    INNER JOIN deluxebb_threads c ON (b.tid = c.tid AND c.fid != 14)
    LEFT JOIN deluxebb_posts d ON d.tid=c.tid AND b.pid < d.pid
    WHERE d.pid IS NULL
    ORDER BY b.pid DESC
    LIMIT 0,6;") or die(mysql_error());
    Thanks!

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2009
    Location
    Ljubljana, Slovenia
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting
    - Inner join if faster than left join if you can turn it around. just don't use sub-queries (u probably know this)

    - Using Views or stored procedures, would add some speed.

    - I don't get this:
    LEFT JOIN deluxebb_posts d ON d.tid=c.tid AND b.pid < d.pid
    WHERE d.pid IS NULL
    - when joining two large tables, butonly searching against one, put the join on the end.
    http://www.mysqlperformanceblog.com/...limit-queries/

    - This article will help you analyse query (and seting keys and indexes if you don't have them):
    http://dev.mysql.com/doc/refman/5.0/...g-explain.html

    - Sometimes a field with formated date helps. No conversion.
    Gregor Grajzar, web developer
    http://xweblabs.com
    http://grajzar.info


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
  •