Hi!
On Jan 23, Alex Krohn wrote:
> Hi,
>
> Has anyone used the fulltext indexes in 3.23 and can share their
> experiences? I've only done limited testing, but was quite disapointed
> with performance.
>
> We have an import of DMoz data into a table with Title, URL and
> Description fields as a fulltext index. There are 2.5 million rows that
> takes up about 600 megs of data.
>
> A search ordered by relevancy takes about 10 seconds which isn't
> acceptable:
>
> mysql> select ID, Title, match(Title,URL,Description) AGAINST ('gossamer threads')
>as x
> mysql> from Links having x order by x desc limit 5;
> +--------+-----------------------------+-----------------+
> | ID | Title | x |
> +--------+-----------------------------+-----------------+
> | 534518 | Gossamer Threads | 26.504848142061 |
> | 529654 | Gossamer Threads | 25.362285817498 |
> | 347215 | Gossamer Threads | 24.619930445728 |
> | 303514 | Gossamer Threads Scripts | 20.181361130953 |
> | 223327 | The Gossamer Project- Fluky | 17.176138340563 |
> +--------+-----------------------------+-----------------+
> 5 rows in set (10.05 sec)
Two points to notice: I'm afraid you're doing full table scan on every
query like this. Try EXPLAIN. Anyway your query is identical to
select ID, Title, match(Title,URL,Description) AGAINST
('gossamer threads') as x from Links where
match(Title,URL,Description) AGAINST ('gossamer threads') limit 5;
which will use FULLTEXT key to retrieve rows.
It's not very pretty as you have to write the same MATCH twice,
but optimizer will notice that the MATCHes are identical and
there will be no additional overhead.
Then, I still have to admit that fulltext search is not as fast as it
should be. We're working to make it faster.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
