Slow query performance

My database contains 20,000 rows and the following query is slow. Please help me to speed this up:

SELECT r.report_id,
       r.user_id,
       u.user_name,
       u.user_mail,
       d.department_name,
       r.report_comment,
       r.report_target_date,
       r.report_create_date,
       r.report_revised_date,
       r.report_root_id
FROM   report r
       JOIN user u
         ON u.user_id = r.user_id
       JOIN department d
         ON u.department_id = d.department_id
WHERE  ( u.user_name LIKE Concat('%', 'hieu', '%') )
       AND ( d.department_name LIKE Concat('%', 'BOM', '%') )
       AND r.report_id IN (SELECT r.report_id
                           FROM   report r
                           WHERE  r.user_id = 1
                           UNION ALL
                           SELECT r.report_id
                           FROM   report r
                                  JOIN user u
                                    ON u.user_id = r.user_id
                                  JOIN department d
                                    ON u.department_id = d.department_id
                                  JOIN authority a
                                    ON r.user_id = a.user_src_id
                           WHERE  a.user_dest_id = 1)
       AND ( r.report_id IN (SELECT r.report_id
                             FROM   report r
                             WHERE  r.report_comment LIKE
                                    Concat('%', 'this', '%'))
              OR r.report_id IN (SELECT rc.report_id
                                 FROM   report_content rc
                                 WHERE  rc.visited_company LIKE
                                        Concat('%', 'this', '%')
                                         OR rc.report_description LIKE
                                            Concat('%', 'this', '%')) )
ORDER  BY r.report_target_date DESC
LIMIT  0, 30 

CREATE TABLE :

CREATE TABLE IF NOT EXISTS `report` (
  `report_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `report_comment` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `report_target_date` datetime NOT NULL,
  `report_create_date` datetime NOT NULL,
  `report_revised_date` datetime DEFAULT NULL,
  `report_revised_id` int(11) DEFAULT NULL,
  `report_root_id` int(11) DEFAULT NULL,
  `enable` int(2) NOT NULL DEFAULT '1',
  PRIMARY KEY (`report_id`),
  UNIQUE KEY `unique_report` (`report_id`),
  KEY `support_report` (`report_id`,`report_target_date`,`report_create_date`,`report_revised_date`,`report_revised_id`,`report_root_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18497 ;


CREATE TABLE IF NOT EXISTS `report_content` (
  `report_content_id` int(11) NOT NULL AUTO_INCREMENT,
  `report_id` int(11) NOT NULL,
  `timestamp` text COLLATE utf8_unicode_ci NOT NULL,
  `visited_company` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `report_description` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`report_content_id`),
  UNIQUE KEY `unique_report` (`report_content_id`,`report_id`),
  UNIQUE KEY `unique_report_content` (`report_content_id`,`report_id`),
  KEY `support_report_content` (`report_content_id`,`report_id`,`visited_company`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=278 ;

My EXPLAIN :

id  | select |   table   |    type   |    possible_keys    |    key   |key_len|    ref  | rows  |  extra
1   |PRIMARY |     r     |    ALL    |         (NULL)      |  (NULL)  | (NULL)| (NULL)  | 20246 | using where/filesort
1   |PRIMARY |     p     |  eq_ref   | PRIMARY,unique_user |  NULL    | 4   |   NULL    |1      | using where
5   |SUBQUERY| report_content|  all  |          NULL       |    NULL  | 4   |   NULL    |160    |  Using where
6   |UNION   | report_content|  all  |          NULL       |    NULL  | 4   |   NULL    |160    |  Using where

NOW I change query following :

    SELECT r.report_id,
         r.user_id,
         u.user_name,
         u.user_mail,
         d.department_name,
         r.report_comment, 
         r.report_target_date,
         r.report_create_date,
         r.report_revised_date,
         r.report_root_id
FROM report AS r FORCE INDEX (unique_report) , `user` 
                AS u FORCE INDEX (unique_user) , `department` 
                AS d FORCE INDEX (unique_department)
WHERE (u.user_name LIKE CONCAT('%', 'hieu', '%')) 
        AND (d.department_name LIKE CONCAT('%', 'bom', '%')) 
        AND r.report_id IN (
SELECT r.report_id
    FROM report r
    WHERE r.user_id =1 UNION ALL
SELECT r.report_id
    FROM report r FORCE INDEX (unique_report)
    JOIN USER u FORCE INDEX (unique_user) ON u.user_id = r.user_id
    JOIN department d FORCE INDEX (unique_department) ON u.department_id = d.department_id
    JOIN authority a FORCE INDEX (unique_authority) ON r.user_id = a.user_src_id
    WHERE a.user_dest_id =1) AND (r.report_id IN (
SELECT  r.report_id
    FROM report r
    WHERE r.report_comment LIKE CONCAT('%', 'when', '%')) OR EXISTS (
SELECT rc.report_id
    FROM report_content rc
    WHERE rc.visited_company LIKE CONCAT('%', 'when', '%') 
    OR MATCH (`timestamp`,report_description) 
    AGAINST('+when+' WITH QUERY EXPANSION) AND r.report_id = rc.report_id))
ORDER BY r.report_target_date DESC
LIMIT 0, 30

AND faster than before : 1.97s.

So somebody help me for speedup more.

MySql is not really my domain, but i believe the basics are the same …

1. Try to use less subqueries
2. Joining user and department tables in the first subquery is completely useless
3. Use EXISTS clause instead of IN (link for explanation)
4. Set up indexes
5. Check the execution plan