How to optimize this SQL query? (Movie Database)

I'm using MySQL and for some reason it takes a lot of time to execute queries like this:

SELECT 
  DISTINCT (Movies.id) AS id, 
  Movies.UnMoID AS UnMoID, 
  Movies.runtime AS runtime
FROM 
  Movies
INNER JOIN Acted ON Acted.UnMoID = Movies.UnMoID
INNER JOIN Actors ON Acted.UnMoActorID = Actors.UnMoActorID
INNER JOIN Directed ON Directed.UnMoID = Movies.UnMoID
INNER JOIN Directors ON Directed.UnMoDirectorID = Directors.UnMoDirectorID
WHERE 
  Actors.name LIKE '%spiderman%'
  OR Directors.name LIKE '%spiderman%'
  OR Movies.originalTitle LIKE '%spiderman%'
  OR Movies.englishTitle LIKE '%spiderman%'
  OR Movies.alsoKnownAs LIKE '%spiderman%'
  OR Movies.taglines LIKE '%spiderman%'
  OR Movies.plot LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20
GROUP BY Movies.id
ORDER BY added DESC

This would give me: "Executed query in 6.5320 seconds and got 2 result(s)."

Explain for this particular query gives:

id  select_type  table     type   possible_keys          key            key_len  ref                            rows  Extra
1   SIMPLE       Movies    ALL    UnMoID                 NULL           NULL     NULL                           925   Using temporary; Using filesort
1   SIMPLE       Directed  ref    UnMoID,UnMoDirectorID  UnMoID         62       Movies.Movies.UnMoID             1    
1   SIMPLE       Directors eq_ref UnMoDirectorID         UnMoDire开发者_StackOverflowctorID 62       Movies.Directed.UnMoDirectorID   1    
1   SIMPLE       Acted     ref    UnMoID,UnMoActorID     UnMoID         62       Movies.Movies.UnMoID            34   
1   SIMPLE       Actors    eq_ref UnMoActorID            UnMoActorID    62       Movies.Acted.UnMoActorID         1   Using where

Profiling info:

......
Creating tmp table    0.000032
Sorting for group     0.000007
executing             0.000005
Copying to tmp table  6.324177
Sorting result        0.000027
Sending data          0.000019
......

In worst cases it takes up to 20 seconds to execute the query and every time most of the time goes to Copying to tmp table. So, what could I do to optimize my queries and to get reasonable query times.

I have indexes for:

Movies.id                 PRIMARY
Movies.UnMoID             UNIQUE
Movies.runtime            INDEX
Acted.UnMoID              INDEX
Acted.UnMoActorID         INDEX
Actors.UnMoActorID        UNIQUE
Actors.name               UINDEX
Directed.UnMoID           INDEX
Directed.UnMoDirectorID   INDEX
Directors.UnMoDirectorID  UNIQUE
Directors.name            INDEX
Movies.originalTitle      INDEX
Movies.englishTitle       INDEX
Movies.alsoKnownAs        INDEX
Movies.taglines           INDEX
Movies.plot               INDEX

Indexes won't help the LIKE searches with leading wildcards. You might want to consider implementing full-text search.

MYSQL like searches are really slow and the fulltext search feature is only available in MyISAM. You can try and optimize things using a read slave that has optimized indices but you won't gain much.

I would move your queries to using a search engine like SPHINX or SOLR(data importer for MYSQL).

I think the reason for the slowness is because your condition is so wide. Instead, I'd try out making simple queries and combine them with UNION, which will also weed out duplicates.

So, something like this might be beneficial (I can't really test that claim because I don't have your DB), because I assume that each individual query will be quite fast, and there will not be many records to merge:

(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies INNER JOIN Actors ON Acted.UnMoActorID = Actors.UnMoActorID
WHERE Actors.name LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies INNER JOIN Directed ON Directed.UnMoID = Movies.UnMoID
INNER JOIN Directors ON Directed.UnMoDirectorID = Directors.UnMoDirectorID
WHERE Directors.name LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.originalTitle LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.englishTitle LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.alsoKnownAs LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.taglines LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
UNION
(SELECT Movies.id, Movies.UnMoID, Movies.runtime, added
FROM Movies 
WHERE Movies.plot LIKE '%spiderman%'
  AND Movies.validated =1
  AND Movies.ageCertificate <=20)
ORDER BY added DESC

You need to include the fields you want to sort on, or to filter on, in your query result.

And if you have an engine that supports subselects, you can move the filter conditions for validation and age certification out of the individual queries and into the common wrapper query, for which the benefit would mainly be removal of repetition, thus, maintenance:

 SELECT id, UnMoID, runtime FROM
 (SELECT ... ) Q   -- the above query including all necessary fields
 WHERE Movies.validated =1
 AND Movies.ageCertificate <=20
 ORDER BY added DESC