0 Members and 1 Guest are viewing this topic.

problem in database Quary usage
« on: 10.05.2022 14:13:47 »
hello my hosting provider said my site have high database Quary"  and It uses a lot of server resources

i use:

jDownloads Version 3.2.46 with Joomla! 3.9.23

if i update jdownload its solved?  or its normal ( i have  1000  Unique visitors per day)

Code: [Select]
SELECT a.file_id, a.file_title, a.file_alias, a.description, a.description_long, a.file_pic, a.images, a.price, a.release, a.file_language, a.system, a.license, a.url_license, a.license_agree, a.size, a.date_added, a.file_date, a.publish_from, a.publish_to, a.use_timeframe, a.url_download, a.preview_filename, a.other_file_id, a.md5_value, a.sha1_value, a.extern_file, a.extern_site, a.mirror_1, a.mirror_2, a.extern_site_mirror_1, a.extern_site_mirror_2, a.url_home, a.author, a.url_author, a.created_id, a.created_mail, a.modified_id, a.modified_date, a.submitted_by, a.set_aup_points, a.downloads, a.cat_id, a.changelog, a.password, a.password_md5, a.views, a.metakey, a.metadesc, a.robots, a.update_active, a.custom_field_1, a.custom_field_2, a.custom_field_3, a.custom_field_4, a.custom_field_5, a.custom_field_6, a.custom_field_7, a.custom_field_8, a.custom_field_9, a.custom_field_10, a.custom_field_11, a.custom_field_12, a.custom_field_13, a.custom_field_14, a.access, a.language, a.ordering, a.featured, a.published, a.checked_out, a.checked_out_time, a.modified_date as modified, a.modified_id,CASE WHEN a.publish_from = 0 THEN a.date_added ELSE a.publish_from END as publish_from,a.publish_to, a.images, a.metakey, a.metadesc, a.access, a.downloads, LENGTH(a.description_long) AS readmore,CASE WHEN badcats.id is not null THEN 0 ELSE a.published END AS state,aa.url_download AS filename_from_other_download,c.title AS category_title, c.access AS category_access, c.alias AS category_alias, c.cat_dir AS category_cat_dir, c.cat_dir_parent AS category_cat_dir_parent,u.name AS creator,u2.name AS modifier,l.title AS license_title, l.url AS license_url, l.description AS license_text, l.id as lid,ROUND(r.rating_sum / r.rating_count, 0) AS rating, r.rating_count as rating_count, r.rating_sum as rating_sum,parent.title as parent_title, parent.id as parent_id, parent.alias as parent_alias,menuf.id AS menuf_itemid,menuc.id AS menuc_cat_itemid,c.published, CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM rr9334_jdownloads_files AS a
LEFT JOIN rr9334_jdownloads_files AS aa on aa.file_id = a.other_file_id
LEFT JOIN rr9334_jdownloads_categories AS c ON c.id = a.cat_id
LEFT JOIN rr9334_users AS ua ON ua.id = a.created_id
LEFT JOIN rr9334_users AS uam ON uam.id = a.modified_id
LEFT JOIN rr9334_users AS u on u.id = a.created_id
LEFT JOIN rr9334_users AS u2 on u2.id = a.modified_id
LEFT JOIN rr9334_jdownloads_licenses AS l on l.id = a.license
LEFT JOIN rr9334_jdownloads_ratings AS r on r.file_id = a.file_id
LEFT JOIN rr9334_jdownloads_categories as parent ON parent.id = c.parent_id
LEFT JOIN (SELECT id, link, access, published from rr9334_menu GROUP BY link) AS menuf on menuf.link LIKE CONCAT('index.php?option=com_jdownloads&view=download&id=',a.file_id) AND menuf.published = 1 AND menuf.access IN (1,1)
LEFT JOIN (SELECT id, link, access, published from rr9334_menu GROUP BY link) AS menuc on menuc.link LIKE CONCAT('index.php?option=com_jdownloads&view=category&catid=',a.cat_id) AND menuc.published = 1 AND menuc.access IN (1,1)
LEFT OUTER JOIN (SELECT cat.id as id FROM rr9334_jdownloads_categories AS cat JOIN rr9334_jdownloads_categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt  AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id
WHERE a.access IN (1,1) AND c.access IN (1,1) AND CASE WHEN badcats.id is null THEN a.published ELSE 0 END = 1 AND a.cat_id IN (4,5,6,7,8,9,10,58,11,12,13,15,60,16,17,18,19,20,21,22,23,55,24,25,26,27,29,30,31,32,33,34,35,36,37,151,158,159,164,167,38,44,45,46,47,48,49,50,51,52,53,54,59,63,64,69,76,78,90,93,66,56,57,61,62,68,91,96,41,70,71,73,75,77,79,80,81,82,83,84,85,87,88,89,92,98,99,156,160,162,165,166) AND (a.publish_from = '0000-00-00 00:00:00' OR a.publish_from <= '2022-05-10 11:33:52') AND (a.publish_to = '0000-00-00 00:00:00' OR a.publish_to >= '2022-05-10 11:33:52')
ORDER BY a.date_added DESC LIMIT 7
Re: problem in database Quary usage
« Reply #1 on: 10.05.2022 14:37:50 »
jD3.9.8.3 is a bit faster and has improved database handling so yes updating would help.

Please read the update article before starting the upgrade - and also make sure you have an uptodate backup.

Also check if you have any Overrides and whether you use any of the extensions listed  in the 'Third Party Extensions' section.

And check what level of PHP you are at and what is available on your server.

I suspect that you are using a much higher volume than originally anticipated and that you may need to upgade the services provided by your hosting service.

Best wishes
Colin M