Mysql – Joining two Mysql VIEWs takes a very long time

join;MySQLperformancequery-performanceview

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 . '%' ));

My question:

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,

Best Answer

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly.

Let's take your query and look for 'tom'

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 '%tom%';

This joins everything. Then, it scans the resulting temp table and returns every row with tom.

Let's implement the FULLTEXT method

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 MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

I can almost assure you that a full table scan will occur during the join because I have experienced it hundreds of times:

Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jul 18, 2012 : Why full-text-search returns less rows than LIKE

I started writing posts about FULLTEXT and query refactoring ever since I saw a post from webmasterworld.com entitled MySQL Match Against + a very expensive join. How do I optimize this?

Given the information I just mentioned let's take your query and refactor it to handle the FULLTEXT and stilldo a good join.

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 MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

First, apply the MATCH ... AGAINST operator to cast_movie and return ImdbId and other fields

SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)

Make that a subquery to provide keys to join and retrieve data from film_info

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

This should produce a better result.

If your tables use InnoDB Storage Engine, remember to tune the InnoDB Fulltext options. Any fulltext option that does not start with innodb_ is meant for MyISAM (See my post MySQL FullText search on string shorter than 3 chars returns no rows)

Give it a Try !!!

Educated guess what you actually want:

CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY r.country, r.destination) AS id
     , c.company_id AS client_name
     , v.company_id AS vendor_name
     , s.c_prefix_id AS c_prefix
     , s.v_prefix_id AS v_prefix
     , r.country
     , r.destination
     , s.c_total_calls
     , s.v_total_calls
     , s.successful_calls
     , s.billed_duration
     , s.v_billed_amount AS cost
     , s.c_billed_amount AS revenue
     , s.c_pdd AS pdd
     , s.profit
     , s.start_time
     , s.end_time
     , s.switch_name
FROM   "Swiss_buz_scdr" s
LEFT   JOIN "Corporate_companyalias" c ON c."Alias_name" = s.client_name_id
LEFT   JOIN "RateManagement_prefix_and_client_wise_destinationgroup" r
         ON r.client_name = c.company_id AND r.prefix = s.c_prefix_id
LEFT   JOIN "Corporate_companyalias" v ON v."Alias_name" = s.vendor_name_id
WHERE  s.customer_name = 'SSP Root';

Aside: I would aim for shorter names than "RateManagement_prefix_and_client_wise_destinationgroup". And preferably legal, lower-case names that don't need double-quotes.

MySQL slow select from a view

There are a few things going on here:

your CREATE VIEW statement contains a SUM() function. Even though your ALGORITHM is specified as MERGE, it is really going to be TEMPTABLE as discussed in the documentation here:

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs: Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
So we're using temporary tables. I suspect you're not seeing 'blocking' due to this benefit of TEMPTABLE algorithm:

A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.
However, the problem with the TEMPTABLE algorithm is that they do not have/use indexes [src]:

View processing is not optimized:

It is not possible to create an index on a view.

Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

So now we need to know the underlying structure of your table as to why the direct query blocks:

If it's not InnoDB, consider switching to use row-level locks instead of table-locks of MyISAM
If there's no index on (number, bet_amount) consider creating one.

If the above points have already been done, and you still for some reason have blocking on a query directly against the salestable table, then you can institute a poor-man's materialized view by creating triggers that modify a temporary table with each number and a running total.

Best Answer

Related Solutions

PostgreSQL 9.1 – Query on VIEWS taking a lot of time

Educated guess what you actually want:

MySQL slow select from a view

Related Question