Search Query Performance in single table and in multiple tables.

[imranlink]

Hi,

I want to optimized my search as much as possible. I am confuse which database structure should i use for optimized search results.

Option 1
---------

If i use a single table to store all data.

CREATE TABLE `songs` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `artist_name` varchar(25) default NULL,
  `album_name` varchar(25) default NULL,
  `song_name` varchar(25) default NULL,
  `language` varchar(25) default NULL,
  `movie_name` varchar(25) default NULL,
  PRIMARY KEY  (`ID`),
  FULLTEXT KEY `searchkey` (`artist_name`,`album_name`,`song_name`,`language`,`movie_name`)
) TYPE=MyISAM;

For Searching I have to use the Query from this single table

SELECT * FROM SONGS WHERE
MATCH(artist_name,album_name,song_name,language,movie_name) 
AGAINST('keywords' IN BOOLEAN MODE)

Option 2
---------
If i use multiple tables with foreign key relation to store data.

CREATE TABLE `artists` (
  `artist_ID` int(10) unsigned NOT NULL auto_increment,
  `artist_name` varchar(25) default NULL,
  PRIMARY KEY  (`artist_ID`),
  FULLTEXT KEY `searchkey` 
(`artist_name`)
) TYPE=MyISAM;


CREATE TABLE `albums` (
  `album_ID` int(10) unsigned NOT NULL auto_increment,
  `album_name` varchar(25) default NULL,
  PRIMARY KEY  (`album_ID`),
  FULLTEXT KEY `searchkey` 
(`album_name`)
) TYPE=MyISAM;

CREATE TABLE `languages` (
  `language_ID` int(10) unsigned NOT NULL auto_increment,
  `language` varchar(25) default NULL,
  PRIMARY KEY  (`language_ID`),
  FULLTEXT KEY `searchkey` 
(`language`)
) TYPE=MyISAM;

CREATE TABLE `movies` (
  `movie_ID` int(10) unsigned NOT NULL auto_increment,
  `movie_name` varchar(25) default NULL,
  PRIMARY KEY  (`movie_ID`),
  FULLTEXT KEY `searchkey` 
(`movie_name`)
) TYPE=MyISAM;


CREATE TABLE `songs` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `artist_ID` int(10) unsigned NOT NULL,
  `album_ID` int(10) unsigned NOT NULL,
  `song_name` varchar(25) default NULL,
  `language_ID` int(10) unsigned NOT NULL,
  `movie_ID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`),
  FULLTEXT KEY `searchkey` 
(`song_name`)
) TYPE=MyISAM;

And for Searching from many tables structure, i have to use the Query

SELECT r.artist_name,a.album_name,s.song_name,l.language,m.movie_name
FROM SONGS as s, albums as a, artists as r, languages as l, movies as m
WHERE
s.album_ID = a.album_ID AND
s.artist_ID = r.artist_ID AND
s.language_ID = l.language_ID AND
s.movie_ID = m.movie_ID AND

MATCH(r.artist_name,a.album_name,s.song_name,l.language,m.movie_name) 
AGAINST('keywords' IN BOOLEAN MODE)

Can you please help me which database structure is good for optimized search result?
Option 1 with single table or Options 2 with many tables?

Regards,
Muhammad Imran Khalid
http://imrankhalid.5u.com