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: http://stackoverflow.com/questions/4629979/nested-select-statement-in-sql-server ? — ShellFish, May 02 '15 at 14:04
when i add aliases it returns #1191 - Can't find FULLTEXT index matching the column list — kutlus, May 02 '15 at 15:21

score 0 · Accepted Answer · answered May 02 '15 at 14:10

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 02 '15 at 14:10

virmaior

424
4
14

#1214 - The used table type doesn't support FULLTEXT indexes – kutlus May 02 '15 at 14:23
temp_movie is not myISAM i guess – kutlus May 02 '15 at 14:24
It worked when i add ENGINE=MyISAM but this time it gave error – kutlus May 02 '15 at 14:30
@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 May 02 '15 at 16:21
@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) – virmaior May 02 '15 at 16:22
Thank you.. I created a single connection at the beginning of everything.. then query the main movie data after that i query the match query that i just builded – kutlus May 02 '15 at 16:34

score 0 · Answer 2 · answered May 02 '15 at 16:09

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

score 0 · Answer 3 · answered May 06 '15 at 09:16

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

Mysql nested match against not returning any results

3 Answers3