Optimize mysql query, on table with 32 million records I have a database that I have created from DISCOG, currently it takes between 32 and 40 seconds to query the database for one record, my query looks similar to;

SELECT MIN(NULLIF(year, 0)) as year, genre, release_date, styles 
FROM track_master 
WHERE (artist LIKE '%Lo%Fidelity%Allstars%') 
  AND (track LIKE '%Battle Flag%')


year, genre, release_date, styles, artist and track are all indexed, when I ran the query with EXPLAIN I received a ALL under TYPE column, which as I've been reading is NOT good, however I do not know what else to do to optimize the query.

Besides the mentioned columns, there is also

id int(25)
release_id int(25)
album_title varchar(2250)
master_id int(25)
main_release int(5)
artist_id int(25)
You need to add full text index to your table as

ALTER TABLE track_master
ADD FULLTEXT INDEX `artist_key` (`artist`)
ADD FULLTEXT INDEX `track_key` (`track`);


Also you need to tweak mysql server settings to support 2 character words. Find the mysql config file, In case of UBUNTU based server it is /etc/my.cnf and then locate the statement innodb_ft_min_token_size=3 and change it to innodb_ft_min_token_size=2. Then restart mysql server.

After that rewrite your query and use it as

SELECT MIN(NULLIF(year, 0)) as year, genre, release_date, styles 
FROM track_master 
WHERE MATCH(artist) AGAINST ('Lo Fidelity Allstars')
AND MATCH(track) AGAINST ('"Battle Flag"')