0

Reading the documentation, I see this mention :

For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

I understand the requirements behind building the index, but how "much [slower]" is it to directly insert data into a table with a FULLTEXT index? Is there actual data to give an idea of this?

I need to create a table which will perform about 2,000 inserts each day, and we expect this amount to climb up to 5,000 inserts in the next years. So, between about 70k and 180k entries each year.

So, I'm trying to assess if I really need to have two tables, i.e. one without an index, and another with a full-text index, or if I can insert directly into the table with the full-text search with negligible impact over time.

There are no DBAs, here, and the current MySQL installation went through a few updates already over the last +decade, so there is nothing fancy going on with the current setup.

Thank you for any useful information you can provide me with.

** Edit **

I typically create tables using the InnoDB engine.

Improve this question

2 Answers 2

Reset to default
1

A few inserts per minute; not a problem. A million rows; not a problem.

I am assuming the table is ENGINE=InnoDB.

The quote that you mention refers to the initial loading of a large number of rows. It sounds like you are starting with an empty table and adding a few thousand each day.

I suggest you do ALTER TABLE tablename ENGINE=InnoDB after a few weeks. This will rebuild the FULLTEXT index. (It seems that FULLTEXT and SPATIAL indexes are not well designed for gradual loading, hence a periodic rebuild may help. Regular indexes do not suffer from this.)

I do not know how (or how much) this rebuild advice will help. But it won't hurt, other than locking up the table for maybe minutes.

If you are actually seeing sluggish inserts, please time one of them for us. Plus provide SHOW CREATE TABLE.

Improve this answer
1
  • Thank you for that information. It may take a while (read years) before we reach millions of rows on that table, but I'll try still be breathing (and work for the same employer) once we get there :)
    – Yanick Rochon
    Commented Dec 2, 2021 at 19:55
1

Not only is the documentation correct in saying this, but this essentially true for any type of index let alone a FULLTEXT index. When bulk loading a table, the fewer indexes you have the faster it loads.

What you seem to be concerned with is the day-to-day inserts, and you are right in your gut feeling.

Your first concern should be having the necessary indexes for a table. If it calls for a FULLTEXT index, make sure you have the with combination of columns. You will also need to focus on structuring your queries so that using a FULLTEXT index is worth it. Sometimes, the MySQL Query Optimizer tends to butt heads with FULLTEXT indexes and ends up ignoring them unless you design the query properly.

Here are some old posts I wrote about it and I adamantly believe hold true even today

Now in terms of any negative impact, writing a good FULLTEXT SELECT for performance outweighs insert performance over time. You many need to run ANALYZE TABLE now and then but you seem to have low enough INSERT traffic not to need to.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.