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:
title
,Album
with a matching title
or artist
,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`)
) ENGINE=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.