The answer is that a fulltext index can only be built on a TEXT field. Even though the mysql documentation describes MEDIUMTEXT and LONGTEXT fields as 'BLOB or TEXT field that can hold..', they can not be used.
On Thu, 2003-06-05 at 09:59, H M Kunzmann wrote:
> Hello All.
>
> I am using Redhat 9.0 with MySQL 4.0.12-0.
>
> I've hit something of a dead-end with fulltext searching and I don't
> know where to look next.
>
> I have a table that is about 1.5GB with about 400 records.
> As you can tell, every record is about 4MB, all of which is
> text.
>
> I've created a fulltext index on the table, with
> > alter table table2002 add fulltext data (data);
> After this is done (takes about 20 minutes to do) I check
> out the index size, which is 8,722 KB...
>
> If I do a fulltext search against this table, it takes about
> 1.5 minutes.
> > select filename from table2002 where match(data) against ('whatever')
>
> If I do an explain on my query, it confirms that the index is used
> +----------+---------+-------------+----+-------+---+----+-----------+
> |table |type |possible_keys|key |key_len|ref|rows|extra |
> +----------+---------+-------------+----+-------+---+----+-----------+
> |table2002 |fulltext |data |data| 0| | 1 |using where|
> +----------+---------+-------------+----+-------+---+----+-----------+
>
> I have another table, sized at only about 50MB, which I use the same
> query and index on, which takes less than a second to do. One thing I
> have noticed, is that the index size on this much smaller table is
> larger than the bigger table (about 9MB).
>
> This is a shot in the dark, but it seems to me that the index size is
> _way_ too small and as such obviously cannot really help. If this is the
> case, how do I increase it ?
>
> Here are some of my mysql settings:
> max_sort_length=16M
> table_cache=256M
> key_buffer=128M
> sort_buffer=4M
> read_buffer_size=1M
>
> Should I set anything else ?
> What can I do to get respectable return times ?
>
> Please lend me a helping hand...
> Thanks in advance.
> H M Kunzmann
--
Herbert Michael Kunzmann
Binary Chaos Magician
signature.asc
Description: This is a digitally signed message part
