Re: Increase the speed of a query that uses a function

[Jay Pipes]

Couple things:

1) Normalize your schema by removing keys which are unnecessary. You can remove the cp_tsklvldes.tldkey field entirely, as the fkey_tskkey and fkey_deskey form a natural primary key.

2) After making the above modification, you should simply use the GROUP_CONCAT function:

SELECT t.taskey, t.task, GROUP_CONCAT(DISTINCT d.designation, d.level SEPARATOR ' ')
FROM cp_task t
INNER JOIN cp_tsklevdes td ON t.tskkey = td.fkey_tskkey
INNER JOIN cp_designation d ON td.fkey_deskey = d.deskey
GROUP BY t.taskkey;

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com