I've three tables inside a MySQL database utilized in a music library application:

The Genre table has posts:

  • id
  • title (string)

The Album table has posts:

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

and also the Track table has posts:

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

Each Album might have a variety of Tracks, each Track has one Album, and every Album has one Genre.


I wish to implement a keyword search that enables the consumer to input a variety of key phrases and discover all Tracks that:

  • possess a matching title,
  • take presctiption an Album having a matching title or artist,
  • or take presctiption an Album having a Genre having a matching title.

Results ought to be sorted by relevancy. It is always good if each area were built with a ranking for relevancy. For instance, the title of the Track is much more important compared to title from the Genre.

Also, the answer should use some type of partial searching. Searching of rubber should first match all Tracks having a title of Rubber, then match Tracks having a title matching *rubber* (*=wildcard), then move onto Albums, and so forth. However, I am not too set on these particulars. I am just searching for a far more general solution will be able to tweak to complement my specific needs.

I ought to also point out that I am utilizing a Light stack, Linux, Apache, MySQL, and PHP.


What's the easiest method to implement this keyword search?


Update: I have been attempting to implement this using a full text search, and also have develop the next SQL claims.

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'd use Apache Solr. Make use of the Data Import Handler to define an SQL query that joins all of your tables together, produce a fulltext index from caused by became a member of data.


The posts named as args to complement() should be the column(s) you defined for that index, within the same order you defined within the index. However, you can't define any index (fulltext or else) across multiple tables in MySQL.

Which means you can't do that:

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

It does not matter whether you are using boolean mode or natural language mode.

You must do this:

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

You may even want to consider my presentation Practical Full-Text Search in MySQL.