OK, I will give you more details.
MySQL ver. : 4.0.16
CPU : 2xCelleron 1000 & 1GB RAM
Table CDS, have 1,053,794 rows, FT index on title,
Data 67,646 KB, Index 70,401 KB
Table ARTISTS, Rows 292,330, FT on name,
Data 8,096 KB
Index 17,218 KB
Table TRACKS, rows 13,841,930, FT on title
Data 625,360 KB
Index 646,672 KB
ft_min_word_len = 3
key_buffer_size 786432000
Explain for both SQLs gives same info :
table type possible_keys key key_len ref rows Extra
artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
Last results that I sent are not correct because I forgot to include one
more join, artists.artistid = cds.artistid, bad oversight I know....
These are the new results :
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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)
Time for second SQL : < 1 sec.
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND 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' )
One more thing that I noticed in last SQL, when I change, in FROM
clause, positions of tables like this : FROM artists, tracks, cds,
instead FROM artists, cds, tracks I get time of 1.9 sec. instead < 1
sec. ?
Regards
-----Original Message-----
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]
Sent: Monday, December 08, 2003 00:02
To: Uros Kotnik
Cc: [EMAIL PROTECTED]
Subject: Re: Speed difference between boolean full-text searches and
full-text searches
Hi!
On Nov 27, Uros Kotnik wrote:
> 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')
> limit 1001
>
> 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)
> limit 1001
>
> Same result but the speed difference is quite a different, why is that
?
What does EXPLAIN show for both queries ?
And are you sure the numbers are correct, the first query - the one
without "IN BOOLEAN MODE" - is faster ? I would expect the opposite.
Regards,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
