Each Tab of a Classified Ad has its own action in the controller that fetches data for that specific tab.
Is it the Overview Tab that you are talking about? If so, have you disable all of the optional blocks for the Overview page so that its only the Ad and not all of the optional extra's?
Can you activate debug mode and then view the debug page for the page that you are viewing. That will display all of the queries involved and the execution time for each query. Anything over 1 is considered a "slow" query (altho, some of that could be database performance and not necessarily a fetching issue).
I always find it best to disable as many things as possible so that you are testing with barebones (and default enables a lot of optional blocks, so unless you already disabled them, there are a lot of them pre-enabled).
Hi Bob,
We just upgraded to MariaDB 10.11
It seems to have sped up ad load times considerably but it's still slower than optimal. I will look at all the queries and see what I can find. Trying to enable debug mode for just my IP but I think cloudflare is messing with it. I have turned off all widgets, "more from this seller", and "more in this category" blocks.
My sysadmin just sent this:
Also the classified query that is slow seems to processing all the comments on ads given there like 2 million to process , Here is a sample query,
| 17080 | xf2014 |
127.0.0.1:34932 | xenforo3 | Execute | 7 | Sending data | SELECT `xf_xa_cas_comment`.*, `xf_xa_cas_ad_Ad_1`.*, `xf_user_User_2`.*, `xf_user_profile_Profile_3`.*, `xf_session_activity_Activity_4`.*
FROM `xf_xa_cas_comment`
LEFT JOIN `xf_xa_cas_ad` AS `xf_xa_cas_ad_Ad_1` ON (`xf_xa_cas_ad_Ad_1`.`ad_id` = `xf_xa_cas_comment`.`ad_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_xa_cas_comment`.`user_id`)
LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_3` ON (`xf_user_profile_Profile_3`.`user_id` = `xf_user_User_2`.`user_id`)
LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_4` ON (`xf_session_activity_Activity_4`.`user_id` = `xf_user_User_2`.`user_id` AND `xf_session_activity_Activity_4`.`unique_key` = CONCAT(`xf_user_User_2`.`user_id`, ''))
WHERE (`xf_xa_cas_comment`.`ad_id` = 1610437) AND ((`xf_xa_cas_comment`.`comment_state` IN ('visible')))
ORDER BY `xf_xa_cas_comment`.`comment_date` ASC, `xf_xa_cas_comment`.`comment_id` ASC
LIMIT 20 | 0.000 |
and here is the explain plan,
MariaDB [xenforo3]> explain extended SELECT `xf_xa_cas_comment`.*, `xf_xa_cas_ad_Ad_1`.*, `xf_user_User_2`.*, `xf_user_profile_Profile_3`.*, `xf_session_activity_Activity_4`.* FROM `xf_xa_cas_comment` LEFT JOIN `xf_xa_cas_ad` AS `xf_xa_cas_ad_Ad_1` ON (`xf_xa_cas_ad_Ad_1`.`ad_id` = `xf_xa_cas_comment`.`ad_id`) LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_xa_cas_comment`.`user_id`) LEFT JOIN `xf_user_profile` AS `xf_user_profile_Profile_3` ON (`xf_user_profile_Profile_3`.`user_id` = `xf_user_User_2`.`user_id`) LEFT JOIN `xf_session_activity` AS `xf_session_activity_Activity_4` ON (`xf_session_activity_Activity_4`.`user_id` = `xf_user_User_2`.`user_id` AND `xf_session_activity_Activity_4`.`unique_key` = CONCAT(`xf_user_User_2`.`user_id`, '')) WHERE (`xf_xa_cas_comment`.`ad_id` = 868721) AND ((`xf_xa_cas_comment`.`comment_state` IN ('visible'))) ORDER BY `xf_xa_cas_comment`.`comment_date` ASC, `xf_xa_cas_comment`.`comment_id` ASC;
+------+-------------+--------------------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+--------------------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------+-----------------------------+
| 1 | SIMPLE | xf_xa_cas_comment | ALL | NULL | NULL | NULL | NULL | 2669449 | 100.00 | Using where; Using filesort |
| 1 | SIMPLE | xf_xa_cas_ad_Ad_1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
| 1 | SIMPLE | xf_user_User_2 | eq_ref | PRIMARY | PRIMARY | 4 | xenforo3.xf_xa_cas_comment.user_id | 1 | 100.00 | |
| 1 | SIMPLE | xf_user_profile_Profile_3 | eq_ref | PRIMARY | PRIMARY | 4 | xenforo3.xf_user_User_2.user_id | 1 | 100.00 | Using where |
| 1 | SIMPLE | xf_session_activity_Activity_4 | eq_ref | PRIMARY | PRIMARY | 22 | xenforo3.xf_user_User_2.user_id,func | 1 | 100.00 | Using where |
+------+-------------+--------------------------------+--------+---------------+---------+---------+--------------------------------------+---------+----------+-----------------------------+
5 rows in set, 1 warning (0.001 sec)
