Match...Against join not working

Databases
1

Hello. I am trying a fulltext search on my database but i’m having some issues with it. I am joining 2 tables and this is the query i’m using:

SELECT songs.song_id, songs.title
FROM songs, song_artist, artists
WHERE songs.song_id = song_artist.song_id
AND song_artist.artist_id = artists.artist_id
AND MATCH (artists.artist, songs.title, songs.lyrics) AGAINST (‘$search’)

describe for table1:

Field---------Type------------------Null------Key-----Default—Extra
song_id------int(10) unsigned--------NO------PRI------NULL-----auto_increment
title----------varchar(400)----------NO-------MUL-----NULL
lyrics---------text------------------NO-------MUL-----NULL
original_id----int(10) unsigned-------YES---------------NULL

describt for table2

Field----------Type-------------------Null------Key-----Default–Extra
artist_id------int(10) unsigned---------NO-------PRI-----NULL-----auto_increment
artist---------varchar(200)------------NO-------MUL-----NULL

all fields i am using in the MATCH() clause are indexed as fulltext:

CREATE TABLE songs(
song_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (song_id),
title VARCHAR(400) NOT NULL,
lyrics TEXT NOT NULL,
FULLTEXT (title, lyrics),
original_id INT UNSIGNED);

CREATE TABLE artists(
artist_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (artist_id),
artist VARCHAR(200) NOT NULL,
FULLTEXT (artist));

CREATE TABLE song_artist(
song_id INT UNSIGNED NOT NULL,
INDEX (song_id),
artist_id INT UNSIGNED NOT NULL,
INDEX (artist_id));

Running that query from phpmyadmin it says “#1210 - Incorrect arguments to MATCH”. Apache says:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\music.php on line 44

Any ideas why the query is not working?

Thanks!

2

Well i made it work but results still are a bit off. For example if i search for “elvis presley fever” it may end up showing on the 2nd page or so. I suspect this is because this search on “artist” would always score pretty high (2/3 words), while it would score a bit lower on “title” (1/3 words). Isn’t there a way to search against some sort merge of artist-title? To index by matching each artist name with each song name and searching against that index?

3

Note you don’t need to use UNION DISTINCT, UNION already removes any duplicate rows while UNION ALL would include duplicate rows from both tables.

4

SELECT * FROM (
SELECT songs.song_id, songs.title
FROM songs, song_artist, artists
WHERE songs.song_id = song_artist.song_id
AND song_artist.artist_id = artists.artist_id
AND MATCH (artists.artist) AGAINST (‘$search’ IN BOOLEAN MODE)

UNION DISTINCT

SELECT song_id, title
FROM songs
WHERE MATCH (title, lyrics) AGAINST (‘$search’ IN BOOLEAN MODE)
) AS blabla

…works. Thanks

5

What engine are you using the the tables, InnoDB, MyISAM, etc? Not all engines support full text search

6

index (a, b, c) it indexes for a, a&b, or a&b&c

that information is wrong IIRC. It would create an index for a,b,c and also if you were searching a, that index could be used because it is the first column. Don’t believe it creates/uses an index for a&b. Perhaps someone else could clear that up.

7

I’m using MyISAM. That’s not the problem. I guess i’ll just have to create two indexes then.

Thanks for your help :slight_smile:

8

Basically i’m thinking to do UNION ALL, this would return results for checks on artist and title separately. Then for those records that have the same id, add their scores, remove duplicates and order by the new score…?

What do you think about this solution?

9

MySQL doesn’t support it, as described here: http://dev.mysql.com/doc/refman/5.0/en/fulltext-restrictions.html

The reason probably is that in the index the values of the two fields are somehow hashed together and thus only work if you query all of the columns in the index.
Not sure about that though, although it would explain it :slight_smile:

10

I deleted the (title, lyrics) index and added an index of just title. Then it worked. Also in my existing title-lyrics index if i do match (title, lyrics) instead of just title it also works.

So basically i can’t match just title in the title-lyrics index. Any idea why?

11

Hm… well i have a book (on “php 6”) and it says that if i index like index (a, b, c) it indexes for a, a&b, or a&b&c. maybe this doesn’t work for fulltext indexes but only for “index”??

12

Hmm you mean i can’t add the reference to the 2nd table inside MATCH()? Doesn’t match…against work with joins? I was trying to do something like this

mysql> SELECT E.entryID, E.title, C.name
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
-> MATCH (E.title, E.entry) AGAINST (‘michigan’) AND
-> E.categoryID=1;

in http://onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html where he seemed to do something similar to what i wanted

How do you suggest i do this then?

Thanks

13

A full text index can’t be made across more than one table, you would thus have to split your search up accordingly.

14

I managed to make it work, however because the results were a bit messy i am trying now to make it order them by relevance score. The “in boolean mode” statement though made all the scores integers and having 100 scores of “1” isn’t really helpful. So i tried to remove it but that made the whole query not function. This is the query i am using:

SELECT song_id, title, score 
FROM (
 SELECT songs.song_id, songs.title, MATCH (artists.artist) AGAINST ('$search') AS score 
 FROM songs, song_artist, artists
 WHERE songs.song_id = song_artist.song_id
 AND song_artist.artist_id = artists.artist_id
 AND MATCH (artists.artist) AGAINST ('$search' IN BOOLEAN MODE)
 
 UNION
 
 SELECT song_id, title, MATCH (title) AGAINST ('$search') AS score 
 FROM songs
 WHERE MATCH (title) AGAINST ('$search' IN BOOLEAN MODE)
)
AS results
ORDER BY score DESC

After some testing i found out that the second sub-select is the one giving me troubles:

SELECT song_id, title, MATCH (title) AGAINST ('$search') AS score 
FROM songs
WHERE MATCH (title) AGAINST ('$search' IN BOOLEAN MODE)

if i remove the “in boolean mode” statement it doesn’t work at all:

SELECT song_id, title
FROM songs
WHERE MATCH (title) AGAINST ('$search')

"Can't find FULLTEXT index matching the column list"

I can’t understand why it doesn’t work without that statement, as basically it’s the same query. I have used this command to index the table:

ALTER TABLE songs ADD FULLTEXT s_titleLyricsIndex (title, lyrics);

I am not using the lyrics column in search at the moment. But from what i understand this should also index for just the title column as well, shouldn’t it?

15

Ok so i am trying this now…

SELECT * (
SELECT songs.song_id, songs.title
FROM songs, song_artist, artists
WHERE songs.song_id = song_artist.song_id
AND song_artist.artist_id = artists.artist_id
AND MATCH (artists.artist) AGAINST (‘$search’ IN BOOLEAN MODE)

UNION DISTINCT

SELECT song_id, title
FROM songs
WHERE MATCH (title, lyrics) AGAINST (‘$search’ IN BOOLEAN MODE)
)

But even though the nested selects work separately, the query as a whole is failing. I tried adding a “FROM songs” (and FROM songs, song_artist, artists) in the end but again it didn’t work