I have a textbox (for which I used jQuery auto-completion) in my PHP form where user can insert an actor/actress name and then by clicking "Search" button, a new window is opened showing list of movies by that actor/actress.
This is a query where I get movies by actor name:
$query = $conn->prepare("SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId WHERE c.castName LIKE :q"); $query->execute(array(':q' => '%' . $searchText . '%' ));
The above query works fine if user select a name from auto-completion list. However I would like to enable user to write any name (even if he couldn't find from auto-completion list). For example, If user write "tom" in the textbox, and click on "search" button, I want to show list of all movies by all actors that their name containing word "tom".
For this purpose, I used
LIKE :q and
':q' => '%' . $searchText . '%' in the above query, but the query never ends!!(I think because cast_movie is a VIEW which is quite large (with 3 million rows) and joining this view with the other table takes a very long time (actually I waited for 10 minutes and it didn't finished yet).
Could someone kindly let me know if there is any way to fix this? (I read we can use index for joining very large tables, but I think it's not possible to define index for cast_movie since it is VIEW.)
More info about tables and views:
cast_movie is a view which is made by joining "movie_roleNames" and "movies".
movie_roleNames is also a view which is made by joining two tables "Cast" and "nameRoles".
film_info is also a view that is made by joining two tables "movies" and "movies_info".
The structure of the above tables:
table "movies": Id, movieName, ImdbId(unique Id of movies), Rate, numVotes, year (indexes: ImdbId, movieName, year)
table "cast": castName, castImdbID (unique Id of casts) (indexes: castName, castImdbID)
table "nameRoles": Id, castImdbId, movieImdbId, role_Id, (indexes: movieImdbId, castImdbId)
VIEW "movie_roleNames": Id, castName, castImdbId, movieImdbId and the join statement was:
SELECT n.Id, c.castName, n.castImdbId, n.movieImdbId FROM nameRoles as n join Cast as c ON n.castImdbId = c.castImdbID
VIEW "cast_movie": Id, castName, castImdbId, movieImdbId, movieName and the join statement was:
SELECT m.Id, r.castName, r.castImdbId, r.movieImdbId, m.movieName FROM movie_roleNames AS r JOIN movies AS m ON r.movieImdbId = m.ImdbId
All ideas are highly appreciated,