Forum Moderators: open

Message Too Old, No Replies

Stop FULLTEXT from re-indexing on every insert?

Effect of a field in a primary key AND in a FULLTEXT index?

         

physics

2:43 am on Apr 2, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I'm having trouble with very slow (over 60 seconds up into the hundreds of seconds) inserts on a table with a large FULLTEXT index. The FULLTEXT index is necessary so that on-site searches are fast (and they are lightning fast).
The admin has become un-useable because of the slow inserts so it has to be resolved, but a full db redesign is out of the question right now.

A couple of questions:

Only one field has a dupe index: sku is a primary key and is in the FULLTEXT index (people search by sku sometimes). Can this slow down inserts?

Is there a setting that can tell MySQL not to re-index the FULLTEXT index automatically on every insert? So I could run a cron job that would execute a command that would trigger a FULLTEXT re-index?

physics

3:13 am on Apr 2, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



OK, I think I fixed this. This is on an old site and the db was not designed well.
It turns out there were three TEXT fields. I moved these to the end of the database. Also, one of the TEXT fields was included in the FULLTEXT index. Turns out a VARCHAR(255) would work OK for this field so I changed it to that. Not sure which of those actions had the greater effect but I'm just glad it's working now.
Just tested an insert and it took 1.5 seconds :)

physics

3:14 am on Apr 2, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I'm still curious about the above questions though.

physics

4:03 am on Apr 5, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



OK, it turns out that the problem is not fixed. I don't know why it was so fast when I tested it at first but now it's taking 2 minutes again. Any advice?