加入兩個 Mysql VIEW 需要很長時間

我的 PHP 表單中有一個文本框（為此我使用了 jQuery 自動完成功能），使用者可以在其中插入演員/女演員姓名，然後點擊“搜尋”按鈕，打開一個新視窗，顯示該演員/女演員的電影列表.

這是我按演員姓名獲取電影的查詢：

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

我的問題：

如果使用者從自動完成列表中選擇一個名稱，上述查詢工作正常。但是我想讓使用者寫任何名字（即使他無法從自動完成列表中找到）。例如，如果使用者在文本框中輸入“tom”，然後點擊“搜尋”按鈕，我想顯示所有演員的所有電影列表，他們的名字包含單詞“tom”。

為此，我在上面的查詢中使用了LIKE :qand ':q' => '%' . $searchText . '%'，但是查詢永遠不會結束！！（我認為因為 cast_movie 是一個非常大的視圖（有 300 萬行），並且將此視圖與另一個表連接起來需要很長時間（實際上我等了 10 分鐘，它還沒有完成）。

有人可以告訴我是否有任何方法可以解決這個問題嗎？（我讀到我們可以使用索引來連接非常大的表，但我認為不可能為 cast_movie 定義索引，因為它是 VIEW。）

有關表和視圖的更多資訊：

cast_movie是通過連接“movie_roleNames”和“movies”而形成的視圖。

movie_roleNames也是一個視圖，它通過連接兩個表“Cast”和“nameRoles”組成。

film_info也是通過連接兩個表“movies”和“movies_info”而形成的視圖。

上表的結構：

表**“電影”**：Id、movieName、ImdbId（電影的唯一 ID）、Rate、numVotes、年份（索引：ImdbId、movieName、年份）

表**“cast”**：castName，castImdbID（演員的唯一 ID）（索引：castName，castImdbID）

表**“nameRoles”**：Id，castImdbId，movieImdbId，role_Id，（索引：movieImdbId，castImdbId）

VIEW “movie_roleNames” : Id, castName, castImdbId, movieImdbId和 join 語句是：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和 join 語句是：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

所有想法都受到高度讚賞，

使用 FULLTEXT 索引必須非常小心。為什麼 ？雖然 FULLTEXT 索引搜尋確實有效，但如果您沒有正確表達查詢，MySQL 查詢優化器往往會建議進行全表掃描。

讓我們接受您的查詢並尋找“湯姆”

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

這連接了一切。然後，它掃描生成的臨時表並返回每一行tom。

讓我們實現 FULLTEXT 方法

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);

我幾乎可以向你保證，在加入過程中會發生全表掃描，因為我已經經歷了數百次：

• Oct 25, 2011：在 BOOLEAN MODE 中忽略 FULLTEXT 索引，條件為“字數”
• Jan 26, 2012：Mysql全文搜尋my.cnf優化
• May 07, 2012：MySQL EXPLAIN 不顯示 FULLTEXT 的“使用索引”
• Jul 18, 2012:為什麼全文搜尋返回的行數少於 LIKE

自從我看到 webmasterworld.com 上的一篇題為MySQL Match Against + a very cost join 的文章後，我就開始寫關於 FULLTEXT 和查詢重構的文章。我該如何優化呢？

鑑於我剛才提到的資訊，讓我們對您的查詢進行重構，以處理 FULLTEXT 並仍然進行良好的連接。

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);

一、將MATCH ... AGAINST運算符應用於cast_movie和返回ImdbId等欄位

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

將其設為子查詢以提供鍵來連接和檢索數據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;

這應該會產生更好的結果。

如果您的表使用 InnoDB 儲存引擎，請記住調整 InnoDB 全文選項。任何不以開頭的全文選項innodb_都適用於 MyISAM（請參閱我的文章MySQL FullText search on string小於 3 chars 返回 no rows）

試一試 ！！！

我不是這方面的專家，但我建議您查看 FULLTEXT 搜尋和索引。據我了解，它們比用於搜尋部分字元串的 LIKE 語句更有效。這是有關該功能的文件。

引用自：https://dba.stackexchange.com/questions/83311