In 6.5, but I think also in previous versions if there are posts in a topic from users which are no longer in the database, the posts are not displayed.
Yet the number of pages of a topic is calculated with the total number of posts. The result is that sometimes for instance a topic has 5 pages, but when you click on the 5th page, the page is empty.
This happens because of a too restricitve query used.
In showflat.php this query:
Code
$query = "<br /> SELECT t1.B_Number,t2.U_Username,t1.B_Posted,t1.B_IP,t1.B_Subject,.........<br /> FROM {$config['tbprefix']}Posts AS t1,<br /> {$config['tbprefix']}Users AS t2<br /> WHERE t1.B_Main = $current<br /> AND t1.B_PosterId = t2.U_Number<br /> $Viewable<br /> ORDER BY B_Number<br /> $Limit<br />";
should be:
Code
$query = "<br /> SELECT t1.B_Number,t2.U_Username,t1.B_Posted,t1.B_IP,t1.B_Subject,.........<br /> FROM {$config['tbprefix']}Posts AS t1<br /> LEFT JOIN {$config['tbprefix']}Users AS t2 ON t1.B_PosterId = t2.U_Number<br /> WHERE t1.B_Main = $current<br /> $Viewable<br /> ORDER BY B_Number<br /> $Limit<br />";
also the way how the number of pages is calculated is unnecessarily complicated.
[]Anno said: LEFT JOIN {$config['tbprefix']}Users AS t2 ON t1.B_PosterId = t2.U_Number WHERE t1.B_Main = $current $Viewable ORDER BY B_Number $Limit ";[/code] [/]
Just curious, have you done any benchmarks?
Chances are that this will slow down the query, so there may be a tradeoff involved - especially of concern to those with large posts tables.
When you LEFT JOIN the query, evaluator can't use underlying indexes on the result table which means the 'WHERE' and 'ORDER BY' clauses will be evaluated by a table scan.
I tend to avoid left joins wherever possible.
The 2nd tweak is nice, I already had implemented something very similar on my site.
Ok, that's gravy then Anno, at least you confirmed it which is the most important part.
With LEFT JOIN you never really can be sure until you run the benchmarks. It depends on the specific query, and the potential speed decrease can vary dramatically from nothing, all the way up to hundreds of times slower, depending on the table structure and what's being done.
Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.
Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
