MySQL Keyword Search Across Multiple Tables

I have three tables in a MySQL database used in a music library application:

The Genre table has columns:

• id
• title (string)

The Album table has columns:

• id
• genre_id (foreign key to Genre.id)
• title (string)
• artist (string)

and the Track table has columns:

• id
• album_id (foreign key to Album.id)
• title (string)

Each Album can have any number of Tracks, each Track has one Album, and each Album has one Genre.

---

I want to implement a keyword search that allows the user to input any number of keywords and find all Tracks that:

• have a matching title,
• are on an Album with a matching title or artist,
• or are on an Album with a Genre with a matching title.

Results should be sorted by relevancy. It would be great if each field had a ranking for relevancy. For example, the title of a Track might be more important than the title of the Genre.

Also, the solution should use some form of partial searching. A search of rubber should first match all Tracks with a title of Rubber, then match Tracks with a title matching *rubber* (*=wildcard), then move on to Albums, and so on. However, I'm not so set on these details. I'm just looking for a more general solution that I can tweak to match my specific needs.

I should also mention that I'm using a LAMP stack, Linux, Apache, MySQL, and PHP.

---

What is the best way to implement this keyword search?

---

Update: I've been trying to implement this via a full text search, and have come up with the following SQL statements.

CREATE TABLE `Genre` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY (`title`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `Genre` VALUES(1, 'Rock');

CREATE TABLE `Album` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `genre_id` int(11) NOT NULL,
  `title` text NOT NULL,
  `artist` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY (`title`, `artist`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `Album` VALUES(1, 1, 'Rubber Soul', 'The Beatles');

CREATE TABLE `Track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `album_id` int(11) NOT NULL,
  `title` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY (`title`)
) ENG开发者_如何学编程INE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `Track` VALUES(1, 1, 'Drive My Car');
INSERT INTO `Track` VALUES(2, 1, 'What Goes On');
INSERT INTO `Track` VALUES(3, 1, 'Run For Your Life');
INSERT INTO `Track` VALUES(4, 1, 'Girl');

---

I would use Apache Solr. Use the Data Import Handler to define an SQL query that joins all your tables together, create a fulltext index from the result of joined data.

---

The columns named as args to MATCH() must be the column(s) you defined for the index, in the same order you defined in the index. But you can't define any index (fulltext or otherwise) across multiple tables in MySQL.

So you can't do this:

WHERE MATCH (g.title, a.title, a.artist, t.title) AGAINST ('beatles')

It doesn't matter whether you're using boolean mode or natural language mode.

You need to do this:

WHERE MATCH (g.title) AGAINST ('beatles')
   OR MATCH (a.title, a.artist) AGAINST ('beatles')
   OR MATCH (t.title) AGAINST ('beatles')

---

You may also be interested in my presentation Practical Full-Text Search in MySQL.

---

Define a fulltext index on the four columns you like to search and then do:

SELECT * FROM genre AS g
  LEFT JOIN album AS a ON g.id = a.genre_id
  LEFT JOIN tracks AS t ON a.id = t.album_id
  WHERE MATCH (g.title,  a.title, a.artist, t.title) AGAINST ('searchstring');

The resullt will be sorted by relevancy. See here for more details on fulltext search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

---

I would use something like Sphinx, u can make an index out of your query and then query that. It's a little difficult to get your head around but the results are 10 times better than mysql AGAINST and you won't have problems later on with speed.