optimize my query mysql
This is your original query

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId
WHERE d.castName LIKE '%castname%'


There are three things you can do

SUGGESTION #1

You should reorganize the query so that the castName is searched first

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId;


SUGGESTION #2

Run these please

SELECT COUNT(1) WithDistinctCount FROM
(SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId) A;
SELECT COUNT(1) WithoutDistinctCount FROM
(SELECT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId) A;


If WithDistinctCount and WithoutDistinctCount are the same, then remove DISTINCT. It may actually return the results faster.

SUGGESTION #3 (Optional)

If you implement my FULLTEXT suggestion (Joining two Mysql VIEWs takes a very long time), you could redo subquery d

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE MATCH(castName) AGAINST ('+castname' IN BOOLEAN MODE)) d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId;


This requires you create a FULLTEXT index on director_movie

ALTER TABLE director_movie ADD FULLTEXT castname_ft_ndx (castname);


GIVE IT A TRY !!!