Re: REPOST: FULLTEXT searching help needed. Please somebody help.
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
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
Hi! On Jun 13, H M Kunzmann wrote: 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. No, this is wrong. Any xxxTEXT field can be used in FULLTEXT index. TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT - they all work. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
When mysql is indexing You can check your data file direcory and You ca see that one file (the index) is growing in size. I see it grow up to 8M and it stays there. Watching this, it grows very slowly. After 5 minutes of indexing, the file size has barely hit 2MB. The command I use is: create fulltext index Name on Table(field) So far I've only used the alter table call... I will use this call next and see if I get any problems. I've used the create index call now, as described above, and it does not change anything. The index still only grows to 8MB. I've read something of MySQL 4.0.12-0 having a fulltext index bug that converts text into char. (MySQL documentation in MySQL 4.0.13-0 docs describes this in a bug fix entry. Perhaps this is a symptom ? I am busy downloading the newer version to find out. No change. Can your system handle files larger than 2GB? Maybe this is the problem. Can anyone give me any indication as to whether this may be the case ? Should I load the kernel that supports bigger filesystems ? Thanks for all help Ciao H M Kunzmann signature.asc Description: This is a digitally signed message part
REPOST: FULLTEXT searching help needed. Please somebody help.
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
I think your index is corrupted because I expect a 1.5 GB index and not 8M!
You can see word list wit a utility (sorry I don't remember te name ft_dump).
I suggest You to drop fulltext index, duplicate database and remove
some rrecords.
Then create index index again.
Some questions:
Do You have disk space to index?
Can your system handle files larger than 2GB?
When mysql is indexing You can check your data file direcory and You
ca see that one file (the index) is growing in size.
The command I use is:
create fulltext index Name on Table(field)
I have some tables with about 300.000 records (about 2k each) and all
works fine:
I rebuild index in minutes and the query needs only 1 or 2 seconds.
Santino
At 9:59 +0200 5-06-2003, 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
--
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]
RE: REPOST: FULLTEXT searching help needed. Please somebody help.
create fulltext index Name on Table(field) i didnt get this
i usually do add fulltext field (field) i think , is that wrong ??
-Original Message-
From: Santino [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 6:14 PM
To: [EMAIL PROTECTED]
Subject: Re: REPOST: FULLTEXT searching help needed. Please somebody
help.
I think your index is corrupted because I expect a 1.5 GB index and not 8M!
You can see word list wit a utility (sorry I don't remember te name
ft_dump).
I suggest You to drop fulltext index, duplicate database and remove
some rrecords.
Then create index index again.
Some questions:
Do You have disk space to index?
Can your system handle files larger than 2GB?
When mysql is indexing You can check your data file direcory and You
ca see that one file (the index) is growing in size.
The command I use is:
create fulltext index Name on Table(field)
I have some tables with about 300.000 records (about 2k each) and all
works fine:
I rebuild index in minutes and the query needs only 1 or 2 seconds.
Santino
At 9:59 +0200 5-06-2003, 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
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
I think your index is corrupted because I expect a 1.5 GB index and not 8M! You can see word list wit a utility (sorry I don't remember te name ft_dump). I agree with this :-) I suggest You to drop fulltext index, duplicate database and remove some rrecords. Then create index index again. I've done this a couple of times already with the same result. Some questions: Do You have disk space to index? I have 22GB free. Can your system handle files larger than 2GB? I'm not sure. Perhaps not with my current kernel. I am using the standard RH9 kernel. When mysql is indexing You can check your data file direcory and You ca see that one file (the index) is growing in size. I see it grow up to 8M and it stays there. The command I use is: create fulltext index Name on Table(field) So far I've only used the alter table call... I will use this call next and see if I get any problems. I've read something of MySQL 4.0.12-0 having a fulltext index bug that converts text into char. (MySQL documentation in MySQL 4.0.13-0 docs describes this in a bug fix entry. Perhaps this is a symptom ? I am busy downloading the newer version to find out. Thank you for your help. I'll get back to you on whether any of the above helped. signature.asc Description: This is a digitally signed message part
