MySQL Forums
Forum List  »  Full-Text Search

Insertion performance for full-text indexed tables and otherwise?
Posted by: Peter Z
Date: December 05, 2004 02:29PM


I need some tips on what kind of performance can be expected from MySQL tables that contain a full text index.

Im working on a system, containing 2 tables, storing logging information for potentially thousands of running programs. One table stores information specific for each log, the other all the entries from all the logs.

The entries contain a TEXT field, on which i need to support full text searches. Most of the other coloumns will also be indexed, by a combination of hash and B-tree indices.

Insertions in the table containing the entries will occur very frequently, we might be talking up to a few megabytes of inserted data per second(ideally anyway, a few thousand client might be sending log info). This data will be inserted in large blocks( approx. a whole log or more at a time, loaded by LOAD DATA INFILE from a local file). The inserted data can be expected to grow to 5-20 gigabyte size and then staying there by throwing out old stuff. The table size in rows could easily run into the millions range.

My worry is whether or not MySQL can handle this at all running on a pretty standard 1-CPU WinXP computer. Searches wont be that frequent(most likely once or twice per day) compared to insertions, but when they do we most certainly doesnt want to wait an hour for the result, but on the other hand i cant have the incoming logging info build up elsewhere because insertions are too slow.

I noticed the MySQL manual mentions that its much faster to add the full text index after inserting the bulk of the data, but i obviously cant do that.

Some tips on expected performance in this scenario, as well as tips on how to best handle this would be very much appreciated.

Btw, do anyone know how the full text index is implemented(i can only remember static full text indexing datastructures)

Options: ReplyQuote

Written By
Insertion performance for full-text indexed tables and otherwise?
December 05, 2004 02:29PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.