MySQL optimization

Question

There are 5 million records in my database .. Table structure:

CREATE TABLE IF NOT EXISTS `music` (
  `id` int(50) NOT NULL auto_increment,
  `artistname` varchar(50) NOT NULL,
  `songname` varchar(50) NOT NULL,
  `duration` varchar(6) NOT NULL,
  `url` varchar(255) NOT NULL,
  `server` int(5) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `artistname` (`artistname`),
  KEY `songname` (`songname`),
  KEY `url` (`url`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

How can I optimize the table and then search the fields "artistname" and "songname"?

Sorry for the bad english. From Russia with love: -D

Answer 1

Create an index FULLTEXT:

CREATE FULLTEXT INDEX fx_music_artist_song ON music (artistname, songname)

and execute the query as follows:

SELECT  *
FROM    mytable
WHERE   MATCH(artistname, songname) AGAINST ('+queen +bohemian' IN BOOLEAN MODE)

This will match both artistname, and songnameeven if the words are not kept.

To match only the artist, you can create an additional index only for artistnameand use it in the query:

SELECT  *
FROM    mytable
WHERE   MATCH(artistname) AGAINST ('+queen' IN BOOLEAN MODE)

, MATCH, , , .

, , .