php - 如何编辑这个非常复杂的导致服务器崩溃的 Joomla 查询

Question

我必须在 Joomla 网站上编辑以下查询。这个查询是如此缓慢和困难，它经常杀死服务器，并且我收到错误网关超时。如果可能，请帮助我编辑它，以避免这么多的左连接，因为我认为这使它变得如此“重”。是:

SELECT 
o.*, o.price AS value, c.name AS catname, g.title AS groupname, u.name AS editor, 
u.id AS userid, u.block as blockuser, t.name as typename, t.id as typeid, 
a.name as agentname, a.id as agentid, image.path as imagepath, 
image.fname as imagefilename, image.type as imagetype, l.title AS language_title,
l.image AS langImage, u.name AS editor     
FROM (mytable_estateagent AS o, mytable_estateagent_categories AS c ) 
LEFT JOIN ( 
    SELECT * FROM mytable_estateagent_images AS img 
        WHERE 1 AND (img.type = '.jpg' OR img.type = '.gif' OR img.type = '.png') 
        AND ( img.ordering= 1 or img.ordering= 0 ) AND img.published = 1 
        GROUP BY img.objid 
    ) 
as image ON (image.objid = o.id)    
LEFT JOIN mytable_estateagent_type AS t ON (o.type = t.id)        
LEFT JOIN mytable_languages AS l ON l.lang_code = o.language     
LEFT JOIN mytable_estateagent_agents AS a ON (o.agent = a.id) 
LEFT JOIN mytable_viewlevels AS g ON (g.id = o.access) 
LEFT JOIN mytable_users AS u ON (u.id = o.created_by) 
LEFT JOIN mytable_users AS uc ON uc.id = o.checked_out 
WHERE 1 AND (c.id = o.cat) ORDER BY id ASC

Answer 1

请参阅上面的评论，但还要注意这一点(已编辑)...

SELECT o.*
...    
LEFT JOIN
 (  SELECT DISTINCT img.objid 
       FROM mytable_estateagent_images img 
      WHERE img.type IN('.jpg','.gif','.png') 
        AND img.ordering IN(1,0) 
        AND img.published = 1
 ) image 
ON image.objid = o.id

...可以(进一步)重写如下...

SELECT DISTINCT o.*
... 
LEFT 
JOIN mytable_estateagent_images image
  ON image.objid
 AND image.type IN('.jpg','.gif','.png') 
 AND image.ordering IN(1,0) 
 AND image.published = 1
...