Another thing that I noticed is : This query takes less than sec :
SELECT artists.name, cds.title, tracks.title FROM artists, tracks, cds WHERE artists.artistid = tracks.artistid AND cds.cdid = tracks.cdid AND MATCH ( name ) AGAINST ( 'madonna' ) But when I add one more AND it takes more than 15 min. SELECT artists.name, cds.title, tracks.title FROM artists, tracks, cds WHERE artists.artistid = tracks.artistid AND cds.cdid = tracks.cdid AND MATCH ( name ) AGAINST ( 'madonna' ) AND MATCH ( cds.title ) AGAINST ( 'music' ) -----Original Message----- From: Tobias Asplund [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:50 To: Uros Kotnik Cc: [EMAIL PROTECTED] Subject: Re: Once again, three queries, same result, huge speed difference On Thu, 4 Dec 2003, Uros Kotnik wrote: > 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 > Can you post EXPLAIN SELECT of those queries as well, please? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
