MySQL Indexing kills my server

[Granto]

I have a ~ 1200000 row database with three 'text' columns. The data is loaded into the database without any problems and there are two indices that I create without any problems.
When I attempt to do a fulltext index on any combination of columns, or even on a single column, my machine crashes after about two hours of processing. I use a dual Pentium II 450 machine with 1GB RAM and plenty of HD space, mySQL 3.23.23 and a later Linux 2.2 release.

Can somebody tell me what I'm doing wrong?

Any advice would be greatly appreciated.

 

[rycamor]

The FULLTEXT index is a brand new feature in MySQL 3.23.23, which is still a beta release. Maybe you should upgrade to the latest release, 3.23.27.

What exactly are your text column types? How big are they? Is there any chance you can optimize more by using TINYTEXT, or VARCHAR?

Also, I don't know if this is true, but there is a user comment on this page in the manual claiming FULLTEXT indexes don't work with BLOB column types (which are essentially the same as TEXT):

http://www.mysql.com/documentation/mysql/commented/manual.php?section=MySQL_full-text_search

 

[Granto]

Yeah, I tried the newest mySQL and it died too. The columns cannot be optimized. Each one is between 200 to 1k bytes in size. The data is first read from the old database using an Erlang-based parser, then turned into a whole bunch of huge text files which are read into the database with the load command; the parser has no knowledge of what is contained within the columns, and even though it could be possible to take out some useless data, when dealing with so many varied records like this it's not feasible to have to write a special case for nearly every second one. They are from an older database system that we are upgrading.
I also tried fulltext with blobs (we started out using blobs for the database), and it doesn't work. That's why we're using text.

In fact, the data was successfully loaded into the table when I created the columns without specifying that they are part of a fulltext index. When I created the index afterwards, it still crashed.

I guess MySQL cannot yet handle this kind of application. It's so unfortunate.

 

[rycamor]

Maybe PostgreSQL

 

[steve1]

Your app sounds EARILY like mine! Right down to ALL the numbers! Mine has a little over 1205000 records though.(although almost 4000 records were in my first version, and I still haven't pared down the table as much as I want.). You wouldn't happen to be writing a search engine?

Look at the bright side! MYSQLs lack of features FORCE you to do things that are more portable! I created another table for an index, and have gotten some queries down to a fraction of a second.

I'm ALSO doing this on a SMALL system, so I am trying to make it EFFICIENT(less memory and less time).

Steve