Optimized List Queries by Xon

Optimized List Queries by Xon 2.12.0

No permission to download
Overview FAQ Updates (24) Reviews (4) History Discussion
imno007 said:
I didn't see any of the previous errors, but certain nodes, for whatever reason, returned a 503 Service temporarily not available error every time they were clicked on, and others did not. Those errors disappeared as soon as I disabled the addon.
Click to expand...
Ugh. Do these have custom node types in them? Can you see any errors in XF error log about it? This may be an add-on conflict :(
 
I haven't done anything to the nodes aside from styling, and I've seen zero errors aside form the 503's, but it does appear to be addon related. I disabled them all except for this one and am not now encountering the issue. I've got a lot of addons, though, so it's going to take me a bit to pinpoint which one.
 
@Xon
Well, it doesn't appear to be an addon conflict after all. I thought I might have found a conflict a couple times, but after disabling the suspect addons the problem would return if I kept clicking through the nodes long enough. It's just that it's random and it doesn't always occur when clicking on the same nodes - so I guess maybe I was just lucky when it didn't happen after disabling all the addons. Or maybe the problem gets worse with whatever other addons enabled, I don't know. Don't worry about it, I'll just wait to see if anyone else comes forward with any similar problems, and if not I'll assume it's something peculiar to my setup. I am on a CyberPanel server, using Litespeed, so maybe it's something to do with that.
 
@Xon Cool add-on. (y) I don't have a forum (let alone one big enough to worry about pagination) but I subscribed to the add-on because I'm interested in large forums and optimization in general. I installed it on my test board and I was wondering about pruning tables in the subquery used for the derived table. For example, this query:
Code: 
SELECT `xf_thread`.*, `xf_deletion_log_DeletionLog_1`.*, `xf_user_User_2`.*, `xf_user_LastPoster_3`.*, `xf_thread_read_Read_4`.*, `xf_thread_user_post_UserPosts_5`.*, `xf_thread_watch_Watch_6`.*
FROM (

SELECT `xf_thread`.`thread_id`
FROM `xf_thread`
LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_1` ON (`xf_deletion_log_DeletionLog_1`.`content_type` = 'thread' AND `xf_deletion_log_DeletionLog_1`.`content_id` = `xf_thread`.`thread_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_LastPoster_3` ON (`xf_user_LastPoster_3`.`user_id` = `xf_thread`.`last_post_user_id`)
LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_4` ON (`xf_thread_read_Read_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_4`.`user_id` = '1')
LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_5` ON (`xf_thread_user_post_UserPosts_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_5`.`user_id` = '1')
LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_6` ON (`xf_thread_watch_Watch_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_6`.`user_id` = '1')
WHERE (`xf_thread`.`node_id` = 2) AND ((`xf_thread`.`discussion_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_thread`.`sticky` = 0)
ORDER BY `xf_thread`.`last_post_date` DESC

LIMIT 10 OFFSET 10
) as `earlyJoinQuery_7`
JOIN `xf_thread` ON (`xf_thread`.`thread_id` = `earlyJoinQuery_7`.`thread_id`)
LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_1` ON (`xf_deletion_log_DeletionLog_1`.`content_type` = 'thread' AND `xf_deletion_log_DeletionLog_1`.`content_id` = `xf_thread`.`thread_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_LastPoster_3` ON (`xf_user_LastPoster_3`.`user_id` = `xf_thread`.`last_post_user_id`)
LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_4` ON (`xf_thread_read_Read_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_4`.`user_id` = '1')
LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_5` ON (`xf_thread_user_post_UserPosts_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_5`.`user_id` = '1')
LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_6` ON (`xf_thread_watch_Watch_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_6`.`user_id` = '1')
ORDER BY `xf_thread`.`last_post_date` DESC
       
LIMIT 10

In this query, you could remove all of the LEFT JOIN tables from the subquery because they are not used in any other clause. MySQL's optimizer doesn't seem to prune those tables automatically either, but my test board is virtually empty so my query plan will certainly differ from an actual install. If you haven't already, you should compare the query plans on a real data set and see if removing the LEFT JOIN tables from the subquery reduces the number of rows read.
 
  • Like
Reactions: Xon
  • LogicException: Unknown relation _db accessed on xf_news_feed
  • src/XF/Mvc/Entity/Finder.php:98
  • Generated by: Unknown account
  • Jun 5, 2019 at 9:07 AM
Stack trace
#0 src/addons/SV/OptimizedListQueries/XF/Finder/NewsFeed.php(18): XF\Mvc\Entity\Finder->__get('_db')
#1 src/XF/Pub/Controller/Member.php(538): SV\OptimizedListQueries\XF\Finder\NewsFeed->beforeFeedId(1731151)
#2 src/XF/Mvc/Dispatcher.php(321): XF\Pub\Controller\Member->actionLatestActivity(Object(XF\Mvc\ParameterBag))
#3 src/XF/Mvc/Dispatcher.php(244): XF\Mvc\Dispatcher->dispatchClass('XF:Member', 'LatestActivity', Object(XF\Mvc\RouteMatch), Object(SV\ElasticSearchEssentials\XF\Pub\Controller\Member), NULL)
#4 src/XF/Mvc/Dispatcher.php(100): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\ElasticSearchEssentials\XF\Pub\Controller\Member), NULL)
#5 src/XF/Mvc/Dispatcher.php(50): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#6 src/XF/App.php(2177): XF\Mvc\Dispatcher->run()
#7 src/XF.php(390): XF\App->run()
#8 index.php(20): XF::runApp('XF\\Pub\\App')
#9 {main}
 
our biggest issue is with the media gallery index page.....as we have over 200k images but the xen dev suggestion was to basically hide them, which, of course we dont want to do.....main page takes 10-15seconds to load tho :(

any possible way something like this could reach over to work with xfmg?
 
dethfire said:
  • LogicException: Unknown relation _db accessed on xf_news_feed
  • src/XF/Mvc/Entity/Finder.php:98
  • Generated by: Unknown account
  • Jun 5, 2019 at 9:07 AM
Stack trace
#0 src/addons/SV/OptimizedListQueries/XF/Finder/NewsFeed.php(18): XF\Mvc\Entity\Finder->__get('_db')
#1 src/XF/Pub/Controller/Member.php(538): SV\OptimizedListQueries\XF\Finder\NewsFeed->beforeFeedId(1731151)
#2 src/XF/Mvc/Dispatcher.php(321): XF\Pub\Controller\Member->actionLatestActivity(Object(XF\Mvc\ParameterBag))
#3 src/XF/Mvc/Dispatcher.php(244): XF\Mvc\Dispatcher->dispatchClass('XF:Member', 'LatestActivity', Object(XF\Mvc\RouteMatch), Object(SV\ElasticSearchEssentials\XF\Pub\Controller\Member), NULL)
#4 src/XF/Mvc/Dispatcher.php(100): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\ElasticSearchEssentials\XF\Pub\Controller\Member), NULL)
#5 src/XF/Mvc/Dispatcher.php(50): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#6 src/XF/App.php(2177): XF\Mvc\Dispatcher->run()
#7 src/XF.php(390): XF\App->run()
#8 index.php(20): XF::runApp('XF\\Pub\\App')
#9 {main}
Click to expand...
Oops, typo. Will fix ASAP

Iggy said:
our biggest issue is with the media gallery index page.....as we have over 200k images but the xen dev suggestion was to basically hide them, which, of course we dont want to do.....main page takes 10-15seconds to load tho :(

any possible way something like this could reach over to work with xfmg?
Click to expand...
Can you open a ticket, and provide some sample SQL and explains? As paid work it shouldn't be too hard but will require some iteration to analyze what is wrong and how to fix it.
 
Bookmarked this to try it when we update our forum from XF 1.x to XF 2.

We have over 27 million posts and a little over 340,000 threads. I'll keep you all posted.
 
Hi @Xon,

now I have get this error after yesterday's update.

Code: 
XF\Db\Exception: MySQL statement prepare error [1221]: Incorrect usage of USE INDEX and FORCE INDEX src/XF/Db/AbstractStatement.php:217
Generated by: xyz 6 Jun 2019 22:35
Stack trace
            SELECT `xf_news_feed`.*, `xf_user_User_1`.*
            FROM `xf_news_feed` FORCE INDEX (`event_date`) USE INDEX (`userId_eventDate`)
            LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_news_feed`.`user_id`)
            WHERE (`xf_news_feed`.`user_id` IN (5, 7730, 11473))
            ORDER BY `xf_news_feed`.`event_date` DESC
        
LIMIT 30
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL statement...', 1221, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(39): XF\Db\Mysqli\Statement->getException('MySQL statement...', 1221, 'HY000')
#2 src/XF/Db/Mysqli/Statement.php(54): XF\Db\Mysqli\Statement->prepare()
#3 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#4 src/XF/Mvc/Entity/Finder.php(1294): XF\Db\AbstractAdapter->query('
            SELECT `xf_...')
#5 src/XF/Pub/Controller/WhatsNew.php(86): XF\Mvc\Entity\Finder->fetch(30)
#6 src/XF/Mvc/Dispatcher.php(321): XF\Pub\Controller\WhatsNew->actionNewsFeed(Object(XF\Mvc\ParameterBag))
#7 src/XF/Mvc/Dispatcher.php(248): XF\Mvc\Dispatcher->dispatchClass('XF:WhatsNew', 'NewsFeed', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\WhatsNew), NULL)
#8 src/XF/Mvc/Dispatcher.php(100): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\WhatsNew), NULL)
#9 src/XF/Mvc/Dispatcher.php(50): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#10 src/XF/App.php(2177): XF\Mvc\Dispatcher->run()
#11 src/XF.php(390): XF\App->run()
#12 index.php(20): XF::runApp('XF\\Pub\\App')
#13 {main}

I saw that error when members click to fiends activities. And disable addon solve the problem :)
 
You must log in or register to reply here.
Back
Top Bottom