our host said its this kind of SQL query who crash the server
SELECT a.id,a.title,a.alias,a.introtext,a.fulltext,a.checked_out,a.checked_out_time,a.catid,a.created,a.created_by,a.created_by_alias,a.modified,a.modified_by,CASE WHEN a.publish_up IS NULL THEN a.created ELSE a.publish_up END AS publish_up,a.publish_down,a.images,a.urls,a.attribs,a.metadata,a.metakey,a.metadesc,a.access,a.hits,a.featured,a.language,LENGTH(a.fulltext) AS readmore,a.ordering,fp.featured_up,fp.featured_down,CASE WHEN c.published = 2 AND a.state > 0 THEN 2 WHEN c.published != 1 THEN 0 ELSE a.state END AS state,c.title AS category_title,c.path AS category_route,c.access AS category_access,c.alias AS category_alias,c.language AS category_language,c.published,c.published AS parents_published,c.lft,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,uam.name AS modified_by_name,parent.title AS parent_title,parent.id AS parent_id,parent.path AS parent_route,parent.alias AS parent_alias,parent.language AS parent_language
FROM s8c9k_content AS a
LEFT JOIN s8c9k_categories AS c ON c.id = a.catid
LEFT JOIN s8c9k_users AS ua ON ua.id = a.created_by
LEFT JOIN s8c9k_users AS uam ON uam.id = a.modified_by
LEFT JOIN s8c9k_categories AS parent ON parent.id = c.parent_id
LEFT JOIN s8c9k_content_frontpage AS fp ON fp.content_id = a.id
WHERE a.access IN (1,5,7) AND c.access IN (1,5,7) AND c.published = 1 AND a.state = 1 AND a.catid IN (103,211,90,91,92,93,94,95,96,98,101) AND (a.publish_up IS NULL OR a.publish_up <= '2023-07-06 13:49:41') AND (a.publish_down IS NULL OR a.publish_down >= '2023-07-06 13:49:41')
ORDER BY a.created DESC LIMIT 3
So as there is category filter IN (103,211,90,91,92,93,94,95,96,98,101)
as the request as a category filter IN (103,211,90,91,92,93,94,95,96,98,101)
who is :
103 = Twiligth Zone
211 = Loisirs
90 = Actualités internationales
91 = Actualités Françaises
92 = Brèves
93 = Sécurité
94 = Jeux Vidéo
95 = Actualités scientifiques
96 = Divers
98 = Actualités High Tech
101 = News flash
here is the proposed fix by joomla.org user :
ou should still fix the query so it is not using so much disk space during the sort. For example, you could use an initial query to select just the id sorted as required and then a second query using the returned ids in the where clause (nothing else) with all the fields you need, also sorted. You could do some timing tests with phpMyAdmin to see how they compare. Here are the results of timing tests with one column and all columns selected from a table with 15000 rows:
Code : Tout sélectionner
Showing rows 0 - 24 (15094 total, Query took 0.0382 seconds.)
SELECT id, camp_name, date_start FROM xxx
ORDER BY date_start DESC;
Showing rows 0 - 24 (15094 total, Query took 1.0973 seconds.)
SELECT * FROM xxx
ORDER BY date_start DESC;
You can see the first query is about 25x faster the second. But does it use less disk space?
you can also exclude archived article from the query this will use less space i guess ?
I can test the modification on xampp lab and then live site if needed
thanks, issue is critical, our host ask this to be fixed and we cannot purchase a vps for 140€ per month
folamour,
