heavy query on sql

[shahiboy]

hello - my hosting say our website run Heavy Query like this on server and may suspend my hosting server

what that and

how can i solved this problem?  (i use joomla 3.9 + jDownloads Version 3.9.8.3.2)  is there anyway to optimize?


SELECT a.id, a.title, a.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.created, a.file_date, a.publish_up, a.publish_down, 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_by, a.created_mail, a.modified_by, a.modified, a.submitted_by, a.set_aup_points, a.downloads, a.catid, a.changelog, a.password, a.password_md5, a.views, a.metakey, a.metadesc, a.robots, a.update_active, a.access, a.user_access, a.language, a.ordering, a.featured, a.published, a.checked_out, a.checked_out_time, a.modified, a.modified_by,CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, 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,u3.name AS user_access_name,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 g8d79_jdownloads_files AS a
LEFT JOIN g8d79_jdownloads_files AS aa on aa.id = a.other_file_id
LEFT JOIN g8d79_jdownloads_categories AS c ON c.id = a.catid
LEFT JOIN g8d79_users AS ua ON ua.id = a.created_by
LEFT JOIN g8d79_users AS uam ON uam.id = a.modified_by
LEFT JOIN g8d79_users AS u on u.id = a.created_by
LEFT JOIN g8d79_users AS u2 on u2.id = a.modified_by
LEFT JOIN g8d79_users AS u3 on u3.id = a.user_access
LEFT JOIN g8d79_jdownloads_licenses AS l on l.id = a.license
LEFT JOIN g8d79_jdownloads_ratings AS r on r.file_id = a.id
LEFT JOIN g8d79_jdownloads_categories as parent ON parent.id = c.parent_id
LEFT JOIN (SELECT id, link, access, published from g8d79_menu GROUP BY link) AS menuf on menuf.link LIKE CONCAT('index.php?option=com_jdownloads&view=download&id=',a.id) AND menuf.published = 1 AND menuf.access IN (1,1)
LEFT JOIN (SELECT id, link, access, published from g8d79_menu GROUP BY link) AS menuc on menuc.link LIKE CONCAT('index.php?option=com_jdownloads&view=category&catid=',a.catid) AND menuc.published = 1 AND menuc.access IN (1,1)
LEFT OUTER JOIN (SELECT cat.id as id FROM g8d79_jdownloads_categories AS cat JOIN g8d79_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.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2023-07-20 18:26:48') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2023-07-20 18:26:48')
ORDER BY a.downloads DESC LIMIT 1

[ColinM]

Hi
It depends how many Downloads you have. Have not heard this previously. Are you using a 'free' site - I use a provider that has a a cost of £40/annum and is ok
Colin