Hi. I’m trying to optimise large imports into a large table with keys. Here is the story so far. Can anyone suggest ways of creating a scalable solution?
I want to allow periodic real time imports of up to 100,000 rows into a table with more than 2,000,000 rows. The table has 2 text indexes.
I use a ‘load data infile’ which is quick. Or was, until the number of rows in the table grew and the inserts slowed down because of index rebuilding (I assume).
I then started disabling and enabling the keys before and after the import, and ran the enable part off as a background process so the user didn’t have to wait for it.
However, ‘enable keys’ locks the table, which makes selects and other operations on that table impossible during the process (currently about 2 mins).
This is a problem. Can anyone suggest how I can get around it. Either using my current method, or using a different method altogether which would be more scaleable.
The table is mysam with fulltext indexes on 3 varchar columns.
My system variables look like this:
set-variable = max_connections=500
init-connect=‘SET NAMES utf8’