View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000550XMB1Bugspublic2019-12-09 06:462020-10-20 18:17
Reportermiqrogroove Assigned Tomiqrogroove  
PrioritynormalSeverityminorReproducibilitysometimes
Status closedResolutionfixed 
Product Version1.9.8 SP2 
Target Version1.9.12Fixed in Version1.9.12 
Summary0000550: Query Optimization in updateforumcount() for Big Boards
DescriptionThe expression "SELECT COUNT(pid)" is significantly not optimal in a test environment with more than 200,000 posts per forum. The phpMyAdmin profiling option indicates more than 1000 ms spent reading tables. The EXPLAIN result shows the posts table indexes were unused.

By switching this to "SELECT COUNT(*)" the query returns the same result in 80 ms (more than 12x faster) by using the fid index instead of scanning the table.

Similarly, the expression "SELECT COUNT(tid)" from forumdisplay.php spends more than 1500 ms reading tables. Switching to "SELECT COUNT(*)" allows the result to come back from the index in 238 ms. I was able to reduce this further to 71 ms by restoring the fid index that no longer exists in version 1.9.11.
TagsNo tags attached.
MySQL Version
PHP Version
Web Server
Browser
Flags
Original Reporter
SVN Revision2777

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2019-12-09 06:46 miqrogroove New Issue
2019-12-29 02:49 miqrogroove Assigned To => miqrogroove
2019-12-29 02:49 miqrogroove Status new => assigned
2019-12-29 03:49 miqrogroove Status assigned => resolved
2019-12-29 03:49 miqrogroove Resolution open => fixed
2019-12-29 03:49 miqrogroove SVN Revision => 2777
2019-12-29 03:49 miqrogroove Fixed in Version => 1.9.12
2020-10-20 18:17 miqrogroove Status resolved => closed