Mysql – trigger to update row number

Your requirements:

The problem is that the user can enter as many values and in whatever order he likes. If he enters less than there are in the database all of them should be removed and replaced with the new ones

suggest that a reasonable option would be your idea:

• DELETE all the rows with same FK,
• then INSERT all the news ones,

both (DELETE and INSERT statements) in one transaction. I tried in SQLdiddle and it worked just fine:

CREATE TABLE Names
(
ID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
ID_ITEM int,
Name varchar(255) UNIQUE
) ;

INSERT INTO Names
  (ID_ITEM, Name)
VALUES
  (1, 'John'),
  (1, 'Amy'),
  (1, 'Joe') ;


START TRANSACTION ;
    DELETE FROM Names
    WHERE ID_ITEM = 1 ;

    INSERT INTO Names
      (ID_ITEM, Name)
    VALUES
      (1, 'John'),
      (1, 'Joe') ;
COMMIT ;

Then:

SELECT * FROM Names ;

returns:

ID   ID_ITEM   Name
--------------------
4     1        John
5     1        Joe

Assuming you are SELECTing only by the PRIMARY KEY, then the following will speed it up:

• NO PARTITIONing. It only slows down such queries, especially because of 1024 partitions.
• Switch to InnoDB. The PRIMARY KEY is "clustered" with the data; this will save a disk hit on each row fetched. Shrink key_buffer_size to 50M and raise innodb_buffer_pool_size to 70% of available RAM. InnoDB also avoids table locks.
• Compress, in the client, the JSON text; the datatype needs to be BLOB (as it mistakenly is now).

Note: InnoDB will expand the disk space by a factor of 2-3; the compression will get that space back. (Please test this with, say, a million rows. Experiment with the different ROW_FORMATs; I don't know which one will work best for your situation. And validate the SELECT speed and the lack of interference between reads and writes.)

Do you really have 5000 connections at the same time? They are probably stumbling over each other and slowing down each other.

You have half turned off the Query cache; also do query_cache_size=0