Mysql嵌套匹配不返回任何结果

I wanted to select sub sets with match against

我想选择匹配的子集

SELECT * FROM (
                SELECT * FROM Movie WHERE 
                MATCH(keywords) AGAINST('$mk')
            )
            WHERE MATCH(genres) AGAINST('$mg')

$mk may be "action,thriller" $keywords may be "hero,guns,forest"

$ mk可能是“动作,惊悚片”$关键字可能是“英雄,枪支,森林”

That doesn't seem to work or i may bee doing something wrong..

这似乎不起作用或我可能做错了什么..

Both queries return values. When i combine match against queries with AND operator it also works.

两个查询都返回值。当我将匹配查询与AND运算符组合时,它也可以工作。

i am trying to achive is to get a set from movies according to keywords then get a set from this subset with matching genres.. and if possible this will go on for some more keys..

我想要实现的是根据关键词从电影中获取一组,然后从这个子集中获得一组具有匹配的类型..如果可能的话,这将继续用于更多的键...

Or is there a better, faster, more modular solution to this? I am having difficulty in understanmding joins i think they may be a better solution but i don't know..

或者是否有更好,更快,更模块化的解决方案?我难以理解连接,我认为它们可能是更好的解决方案,但我不知道..

Regards

3 个解决方案

#1

The indexing performance on your current method is going to be exceptionally poor.

您当前方法的索引性能将非常差。

Instead, create a temporary table, index that, and full text against that.

而是创建一个临时表,索引和相应的全文。

CREATE TEMPORARY TABLE `temp_movie`    
SELECT * FROM Movie WHERE 
                MATCH(keywords) AGAINST('$mk');

ALTER TABLE `temp_movie` ADD FULLTEXT INDEX(genres);

SELECT * FROM `temp_movie`
            WHERE MATCH(genres) AGAINST('$mg')

And this should perform faster. Depending on the situation you may want to use non-temporary tables and manage caching them.

这应该更快。根据具体情况,您可能希望使用非临时表并管理缓存。

#2

In the end i managed to get results but only to be seen in phpMyAdmin;

最后我设法得到了结果,但只能在phpMyAdmin中看到;

the example code is as follows

示例代码如下

CREATE TEMPORARY TABLE `temp_movie` ENGINE=MyISAM
    SELECT * FROM Movie WHERE MATCH(keywords) AGAINST('$mk');
        ALTER TABLE `temp_movie` ADD FULLTEXT INDEX(genres);
CREATE TEMPORARY TABLE `temp_movie2` ENGINE=MyISAM
    SELECT * FROM `temp_movie` WHERE MATCH(genres) AGAINST('$mg');
        ALTER TABLE `temp_movie2` ADD FULLTEXT INDEX(director);
    SELECT * FROM `temp_movie2` WHERE MATCH(director) AGAINST('$md');

1- I get matching keywords to another temp table1 2- I get matching genres to another temp table2 from table1 3- I get matching directors as a list from table2

1-我得到匹配的关键字到另一个临时表1 2-我从表1得到匹配的类型到另一个临时表2我得到匹配的导演作为表2的列表

It works in SQL query test in phpMyadmin BUT

它适用于phpMyadmin中的SQL查询测试

  if($result = $conn->query($simquery)){
    while($similar_movie = $result->fetch_assoc()) {
    echo $similar_movie["original_title"]."<br>";
    echo "test";
    }
}

Wont do anything.. ?!? it is a PHP question now i think but can anyone tell whats wrong..?

什么都不做..?!?现在我认为这是一个PHP问题,但任何人都可以告诉我们错了什么...?

Regrads

#3

At last i managed this.. Created 3 temporary tables with different selections selected by MATCH AGAINST UNION these temp tables by id and sum of scores Select top matches from the final temp table :))

最后我管理了这个..创建3个临时表,由MATCH AGAINST UNION选择不同的选项这些临时表由id和得分总和选择最终临时表中的*匹配:))

Here's the code and thank to all who helped..

这是代码,感谢所有帮助过的人。

$simquery = "
create temporary table t1
SELECT *, MATCH(keywords) AGAINST('$mk') as score from Movie ORDER BY score DESC;
";
$simquery2 = "
create temporary table t2
SELECT *, MATCH(genres) AGAINST('$mg') as score from Movie ORDER BY score DESC;
";
$simquery3 = "
create temporary table t3
SELECT *, MATCH(overview) AGAINST('$mo') as score from Movie ORDER BY score DESC;
";
$simfinal = "
SELECT *, sum(score) FROM 
(
 SELECT * FROM t1
 UNION 
 SELECT * FROM t2
 UNION 
 SELECT * FROM t3
) as tmp
WHERE tmdb_id != ".$id." GROUP BY id ORDER BY score DESC  LIMIT 30;
";

$conn2->query($simquery);
$conn2->query($simquery2);
$conn2->query($simquery3);

$result = $conn2->query($simfinal);

#1