How to implement Mysql full text search on a big website

2007-09-09

How to implement Mysql FULL TEXT search on a big website, with million of rows and multiple tables. In case you didn't know, you can't use FULL TEXT when joining two tables, and in most of the cases you have multipe tables to search(normalization) . Here is how you can implement your search, I will show this on a real world example.

Let's begin with my test database structure.I have two tables, one for artist name and one for song name

-- 
-- Table structure for table `artists`
-- 

CREATE TABLE `artists` (
  `id` int(11) NOT NULL auto_increment,
  `artist` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `artistindex` (`artist`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT;

-- 
-- Table structure for table `songs`
-- 

CREATE TABLE `songs` (
  `id` int(11) NOT NULL auto_increment,
  `song` varchar(255) NOT NULL,
  `composer` varchar(255) NOT NULL,
  `artist` int(11) NOT NULL,
  `views` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `artistindex` (`artist`,`song`),
  FULLTEXT KEY `songindex` (`song`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT;

-- 
-- Table structure for table `songs`
-- 

CREATE TABLE `albums` (
  `id` int(11) NOT NULL auto_increment,
  `albumname` varchar(255) NOT NULL,
  `artist` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `albumnameindex` (`albumname`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT;

To show all songs of artist 'Maddona' I used the following sql

SELECT * FROM artists,songs,albums WHERE artists.id = songs.artist AND albums.artist = artists.id AND artists.name = 'Madonna'

I used an sql join to display all songs of Madonna, but how can we search the database for a phrase like this 'Madonna - Frozen' ? the following sql statement doesn't work

SELECT *
FROM artists, songs
WHERE artists.id = songs.artist
MATCH (
artists.name, songs.song
)
AGAINST (
'Maddona - Frozen'
)

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use near
 'MATCH (artists.name , songs.song ) AGAINST ( 'Madonna - Frozen' )' at line 1

Of course, there is a simple way, put all data in one big table, but this violates normalization, and your table will be very big and slow.

The better way is to create a search table, a table that holds down a row for each song that contains song name, artist name and, if desired, album name. Something like the following

-- 
-- Table structure for table `search`
-- 

CREATE TABLE `search` (
  `song_id` int(11) NOT NULL,
  `text` varchar(255) NOT NULL,
  PRIMARY KEY  (`song_id`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The only disadvantage to this is that for every song added, you must insert data also in the `search` table. But, the advantages are that your searches are really fast because this table is smaller than the song table (especially in real world you can include additional fields like lyrics, review etc.).

The idea behind this is very simple. You build a special table that holds a row which includes all your keywords, and a row indicating id of resource to point to.

SELECT * FROM search WHERE MATCH `text` against('Madonna Frozen Ray of Light')

How to implement Mysql full text search on a big website

Related

Comments

Make yourself heard

Categories

How to implement Mysql full text search on a big website

Share this with the world

Related

Comments

Make yourself heard

Categories

Subscribe