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)
| improve this question | | | | |
  • Your WHERE conditions cause full table search (no index can be used to optimize them). So the only way to optimize this query may be CREATE INDEX idx_name ON track_master (artist, track) - it can replace table scan with index scan as a "compact table". Besides the mentioned columns, there is also You store both artist and artist_id in this table? Why artist (name?) is not stored in separate table? int(25) strange datatype - integer cannot store so much digits, 11 max (including negative sign)... – Akina Sep 3 '18 at 4:43
  • @Akina: int(25) is valid syntax for MySQL as can be verified with the following db<>fiddle – John aka hot2use Sep 3 '18 at 5:58
  • @hot2use Yes, it is valid... but it does not make sense. – Akina Sep 3 '18 at 7:02
  • artist_id is stored in this table, because there is also a table called artist_name_variations, just in case the tag has a different variation of the name. – DmVinny Sep 3 '18 at 15:26
  • after the search goes through all the files, the ones it did not find can be then searched using variations, if I did it at the same time each search was running about 2 1/2 minutes per song. – DmVinny Sep 3 '18 at 15:33

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"')
| improve this answer | | | | |
  • AGAINST ('Lo Fidelity Allstars') - Lo token will be ignored with default full text settings (innodb_ft_min_token_size=3). And some records which do not match the author's request will be found. – Akina Sep 3 '18 at 7:15
  • You can always add the original criteria to the were clause given by Kapil. – Gerard H. Pille Sep 3 '18 at 8:45
  • @Akina I know this fact but this requires tweaking server settings, Do you this suggesting server settings at the same time is good idea? – Kapil Bhagchandani Sep 3 '18 at 8:53
  • I'd like to say that the task which is solved by your query slightly differs from the task the author is searching the answer for... – Akina Sep 3 '18 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.