Thank you zyspec!
You are the master of XOOPS CORE API.
I take a look at that class but it seems it also have some limitations. it just accept one LEFT JOIN and no INNER JOIN.
as for WHERE EXISTS i decide to not use it in newbb and use some other more efficient queries.
XOOPS CORE and Modules are full of examples of "LEFT JOIN"
in newbb i have this:
// this code in class/post.php
$sql = 'SELECT p.*, t.* '.
' FROM ' . $this->db->prefix('bb_posts') . ' AS p'.
' LEFT JOIN ' . $this->db->prefix('bb_posts_text') . " AS t ON t.post_id = p.post_id";
// other codes in viewpost.php in different places
$criteria_post = new CriteriaCompo(new Criteria("p.forum_id", "(".implode(",",$access_forums).")", "IN"));
$criteria_post->setSort("p.post_id");
$criteria_post->setOrder($order);
$criteria_post->add(new Criteria("p.approved", 1)); // irmtfan uncomment
$join = ' LEFT JOIN ' . $xoopsDB->prefix('bb_reads_topic') . ' AS r ON r.read_item = p.topic_id AND r.uid = ' . $read_uid . ' '; // irmtfan corrected add AS
$criteria_status_post = new CriteriaCompo();// irmtfan new criteria
$criteria_status_post->add(new Criteria("p.post_id", "r.`post_id`", ">")); // irmtfan corrected - should use $value="r.``" to render in XOOPS/class/criteria.php
$criteria_status_post->add(new Criteria("r.read_id", NULL, "IS NULL"), "OR");// irmtfan corrected - should use "IS NULL" to render in XOOPS/class/criteria.php
$criteria_post->add($criteria_status_post); // irmtfan add the status criteria to post criteria
$sql and $join are hardcoded in the above codes.
There are 2 LEFT JOIN:
the first left join select only one row from bb_posts_text table (very huge table in my website it is 400MB)
the second left join only select one row from bb_reads_topic.( maybe large number of rows one row = one topic per user)
As you can see queries in newbb are complicated.
an example of above query is:
0.210036 - SELECT p.*, t.* FROM bb_posts AS p LEFT JOIN bb_posts_text AS t ON t.post_id = p.post_id LEFT JOIN bb_reads_topic AS r ON r.read_item = p.topic_id AND r.uid = 1 WHERE (p.forum_id IN (1,2,4) AND p.approved = '1' AND (p.post_id > r.`post_id` OR r.read_id IS NULL) ) ORDER BY p.post_id DESC LIMIT 0, 5
As you can see we can consider that running time high as 0.210036 seconds
so the real and specific question is:
is there any better query than above? can we achieve better performances by using XOOPS CORE getByLink($criteria)?
