Re: random-access I/O

[Rick James]

> I have read several articles that mention that by compressing the tables you increase the insertion time.
Can you give me some URLs? I would like to argue with them. The cost in CPU time is usually cheaper than saved I/O time.

I'm still puzzled by this:
> PRIMARY KEY (`id`)
Did you mean id_document ??

Assuming that, then...
* 1000 id_document values in the IN list.
* 100 disk hits per second (Rule of Thumb for ordinary disks)
* Data is so big, that caching is not very effective (therefore, most fetches will need to hit the disk)
* BLOBs are stored separately (doubling the disk hits)

That adds up to about 20 seconds for the SELECT. This is close enough to your observed 40 seconds for me to belive that I have analyzed it correctly.

Oops, if you are not touching the blobs, the last bullet item is irrelevant. Hmmm... down to 10 sec.

> CPU usage is very low
Another clue that I/O is the problem.

> HD is not used at all
That is puzzling. Please explain how you arrived at that?

> 'innodb_buffer_pool_size', '1074790400'
That is good if your machine has at least 2GB of RAM. How much do you have available for MySQL?

What version of MySQL?

This somewhat confirms the other things you have said:
> 1, 'SIMPLE', 'documentlist', 'range', 'PRIMARY', 'PRIMARY', '4', '', 1000, 'Using where'

Both old and new tables are InnoDB?
What differences are there in the CREATE TABLEs?