Re: Innocent (looking) query pinning mysqld

[gerald_clark]

You have no ON condition for image_cat_location_link.

Chris Cameron wrote:

The gist; the below query doesn't finish, can't figure out why.

-- Query --
SELECT
        images.iid,
        images.locnetid,
        images.path,
        images.CD,
        lightbox_location_image_LINK.iid
FROM
        images
CROSS JOIN
        image_cat_location_link
LEFT OUTER JOIN
        lightbox_location_image_LINK
ON
        images.cid = lightbox_location_image_LINK.cid
AND
        images.lid = lightbox_location_image_LINK.lid
WHERE
        image_cat_location_link.lid = 3430
AND
        image_cat_location_link.cid = 278
ORDER BY
        images.iid
'show processlist' while this query is running:
---
| 982 | root     | localhost | loctest  | Query   | 31   | Copying to
tmp table | SELECT images.iid, images.locnetid, images.path, images.CD,
lightbox_location_image_LINK.iid FROM im |
                                                                                                                                                                                           
                                                                                                                                                                                           
CREATE definition on 3 tables involved:
---
CREATE TABLE images (
 iid int(10) unsigned NOT NULL auto_increment,
 lid int(10) unsigned default NULL,
 locnetid varchar(20) default NULL,
 CD varchar(50) default NULL,
 path longtext NOT NULL,
 hero tinyint(1) default NULL,
 cid int(10) unsigned default NULL,
 PRIMARY KEY  (iid),
 UNIQUE KEY iid (iid),
 KEY IX_images (iid),
 KEY lid_index (lid),
 KEY cid_index (cid)
) TYPE=MyISAM;
                                                                                                                                                                                           
CREATE TABLE lightbox_location_image_LINK (
 lightid int(10) unsigned NOT NULL default '0',
 cid int(10) unsigned NOT NULL default '0',
 lid int(10) unsigned NOT NULL default '0',
 iid int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (lightid,cid,lid,iid),
 KEY lightid_index (lightid),
 KEY lid_index (lid),
 KEY cid_index (cid)
) TYPE=MyISAM;
                                                                                                                                                                                           
CREATE TABLE image_cat_location_link (
 iid int(10) unsigned NOT NULL default '0',
 cid int(10) unsigned NOT NULL default '0',
 lid int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (iid,cid,lid)
) TYPE=MyISAM;

I'm unable to run EXPLAIN on this query as it also hangs. Also, I've
been fiddling with indexes, so they may be a little out of whack.. But I
do think the right things are indexed.
Any help would be appreciated.

Thanks,
Chris
 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]