Inefficient PHP Code?

[britton]

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?

$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!

[gregor171]

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.