Mysql nested match against not returning any results

Question

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"

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.

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

Try aliasing the inner select statement as seen in: stackoverflow.com/questions/4629979/… ? — ShellFish
– ShellFish, Commented May 2, 2015 at 14:04
when i add aliases it returns #1191 - Can't find FULLTEXT index matching the column list — kutlus
– kutlus, Commented May 2, 2015 at 15:21

virmaior · Accepted Answer · 2015-05-02 14:10:50Z

0

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.

answered May 2, 2015 at 14:10

virmaior

4244 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

kutlus Over a year ago

#1214 - The used table type doesn't support FULLTEXT indexes

kutlus Over a year ago

temp_movie is not myISAM i guess

kutlus Over a year ago

It worked when i add ENGINE=MyISAM but this time it gave error

virmaior Over a year ago

@kutlus it depends on your MySQL version. You can also use a regular table rather than temporary table but then you need to clean it up afterwards.

virmaior Over a year ago

@kutlus the problem you're getting in PHP is that temporary tables last only as long as the connection does, so you need to do them all on the same connection or not use MySQL temporary tables (instead you would need to manage table clean up yourself)

|

kutlus · Accepted Answer · 2015-05-02 16:09:31Z

In the end i managed to get results but only to be seen in 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

It works in SQL query test in phpMyadmin BUT

  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..?

Regrads

kutlus · Accepted Answer · 2015-05-06 09:16:35Z

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

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

Collectives™ on Stack Overflow

Mysql nested match against not returning any results

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related