SELECT dc.URN,dc.GuideName,dc.GuideStreet,ss.categorycalc,pm.initiatedate
FROM dc
LEFT JOIN pm
ON dc.URN = pm.URN
LEFT JOIN ss
ON dc.URN = replace(ss.URN,"/","-")
WHERE dc.GuidePCode LIKE 'WA9%'
ORDER BY pm.Status ASC;

EXPLAIN SELECT dc.URN,dc.GuideName,dc.GuideStreet,ss.categorycalc,pm.initiatedate
FROM dc
LEFT JOIN pm ON dc.URN = pm.URN
LEFT JOIN ss ON dc.URN = replace(ss.URN,"/","-")
WHERE dc.GuidePCode LIKE 'WA9%'
ORDER BY pm.Status ASC;

ALTER TABLE dc ADD INDEX URNIdx (URN);
ALTER TABLE pm ADD INDEX URNIdx (URN);
ALTER TABLE dc ADD INDEX GuidePCodeIdx (GuidePCode);

UPDATE ss SET URNdashed = replace(ss.URN, "/", "-");

ALTER TABLE ss ADD INDEX URNdashedIdx (URNdashed);

ON dc.URN = replace(ss.URN,"/","-")
to
ON dc.URN = URNdashed