I posted this few days ago, but with no answer, also posted it to
benchmark list..
Executing this SQL, takes ~5 sec.
select artists.name , cds.title , tracks.title from artists, tracks,
cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna')
and MATCH (cds.title) AGAINST ('music')
and MATCH (cds.title) AGAINST ('mix')
and MATCH (cds.title) AGAINST ('2001')
and this, ~40 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('"music mix 2001"' IN BOOLEAN MODE)
and executing this takes less than 1 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and artists.name like '%madonna%'
and cds.title like '%music mix 2001%'
Same result but the speed difference is quite a different, why is that ?
This is only on test DB, I didn't try it on real life DB where I have
~14 mil. rows in tracks table.
Regards
