User avatar
zag
Site Admin
Site Admin
Posts: 1246
Joined: Wed Jun 06, 2012 9:19 am
Country: United Kingdom

MySQL Index help

Fri Apr 26, 2013 10:06 am

I have this query

Code: Select all

EXPLAIN SELECT *
FROM album
WHERE strArtist LIKE  '%José Afonso%'
AND strAlbum LIKE  '%Galinhas do Mato%'
ORDER BY LENGTH( strAlbum )
LIMIT 25


But it shows that there are no indexes available to be used.

I have created an index using:

Code: Select all

ALTER TABLE album ADD INDEX (strArtist, strAlbum);


Any ideas why its not using the index properly? It works fine with single column indexs so I'm a little confused.

User avatar
zag
Site Admin
Site Admin
Posts: 1246
Joined: Wed Jun 06, 2012 9:19 am
Country: United Kingdom

Re: MySQL Index help

Fri Apr 26, 2013 10:15 am

After a few tests this works

Code: Select all

SELECT *
FROM album
WHERE strArtist LIKE  'José Afonso'
AND strAlbum LIKE  'Galinhas do Mato'
LIMIT 0 , 30


So I guess you can't use % wildcards.

User avatar
Pernod
Moderator
Moderator
Posts: 290
Joined: Thu Jun 21, 2012 2:21 pm
Country: United Kingdom
Location: UK
Contact: Website

Re: MySQL Index help

Fri Apr 26, 2013 10:23 am

I believe you need to use FULLTEXT indexes when using LIKE with wildcards.

Code: Select all

ALTER TABLE album ADD FULLTEXT INDEX strArtist;
ALTER TABLE album ADD FULLTEXT INDEX strAlbum;

User avatar
zag
Site Admin
Site Admin
Posts: 1246
Joined: Wed Jun 06, 2012 9:19 am
Country: United Kingdom

Re: MySQL Index help

Fri Apr 26, 2013 10:38 am

Perfect! Thanks.

The website Artist page should load about 100% faster now :)

Also the SearchAlbum api has increased in speed by 800%

Happy days!

Return to “Developers”