Query problem with url_alias tabel
dummwiam - November 7, 2009 - 15:47
I am having performance issues with the following query, that collects the latest nodes that had been commented...
SELECT c.subject AS title, c.name AS name, c.comment AS body, c.nid, c.cid, c.timestamp AS date , n.nid AS nid, n.title AS nTitle, n.type AS type, url.dst as LINK
FROM comments c,
node n
LEFT JOIN url_alias url ON SUBSTRING(url.src,6) = n.nid
WHERE
n.status = 1
AND n.nid = c.nid
ORDER BY
c.cid DESCThe whole problem is that SUBSTRING doesn't work well with LEFT JOIN's but I cannot find another way of matching with the URL_ALIAS table since
A) url_alias.pid has different number from node.nid and I cannot use the following:
LEFT JOIN url_alias url ON url.pid = n.nidB) Some nodes do have a value on url_alias.dst and others dont'!
Any advice/help/enlightenment would be appreciated.
This might be a little
This might be a little better:
LEFT JOIN url_alias url ON url.src = CONCAT('node/', n.nid)Thanx :)
Same delays, I found somewhere that in JOINS MySQL functions are take more resources than normally!
I think the main problem is that url_alias.pid is not equal to node.nid, is that normal or is it a bug?