MySQL query performance

Hi All, We have a MySQL db with a query which looks up data through joins from huge tables (more than 6 million records). I get disctinct counts or do substring_index() multiple times to parse a column string and retrieve the required text. then I use this data to populate flash based fusion charts in a grails-groovy application.

Right now the complex graphs are getting timed out, simple ones which access smaller tables load properly, so How do I Improve query performance for the complex graph. We are trying to parse the data before storing it into the table so we dont need to do substring_index on columns, and we are having indexes on the primary keys and that long string column. Here is one sample query for a graph:

    Select count(m.cnt) as
dist_object_cnt,m.employeeid from 
(SELECT
distinct(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
`transactiontext` , '<-->', 2
),'<-->',-1),'OBJECTNAME=',-1)) as
cnt, 
SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
`transactiontext` , '<-->', 3
),'<-->',-1),'OBJECTTYPE=',-1) AS
OBJECTTYPE, 
U.EMPLOYEEID,U.FIRSTNAME,U.LASTNAME,
U.TITLE,U.MANAGER,
DATE_FORMAT(UT.DATE, '%b %d, %Y') FROM
USER_TRANSACTION UT, USERS U,
TRANSACTION T, RESOURCE R WHERE  DATE
BETWEEN '2011-08-01' AND '2011-08-20'
AND U.ID = UT.USER_ID AND UT.TRANS_ID
= T.ID AND T.TRANSACTIONTEXT LIKE '%<-->%' AND R.ID = UT.RESOURCE_ID AND
R.RESOURCENAME = 'Agile'  ORDER BY
u.lastname desc, u.firstname asc) m
group by m.employeeid order by
dist_object_cnt desc limit 100;