Re: tables with 3,500,000 rows, indexed but still v slow
"first process" -- 'first' relative to what? Does TRADE have millions of rows when the 'first' process is run? Is MySQL freshly started up? Other?
The SELECT you quoted hits a lot of tables.
The INSERTs -- are they into just TRADE, or into all the tables?
The UPDATEs -- do they ever change the value of a PRIMARY KEY?
Of your 20K queries, estimate how many times some index is 'touched'. By touched, I mean:
SELECT -- the JOIN...ON touches one index; WHERE probably touches one index.
INSERT -- all indexes of the table
UPDATE -- probably only 1 index, the one needed to find the row(s).
Also, when doing an UPDATE, does it hit one row, or many? Estimate the total number of rows hit.
For SELECTs and UPDATEs, the WHERE clause (etc) may need to scan through unnecessary rows to find the desired ones. Give some kind of estimate of this.
Where am I leading with all this?
* To INSERT a row, one block of each index must be read, modified, and written.
* For SELECTs, index(es) are read
etc.
All of this adds up to disk I/O. But that I/O is softened by caching (the buffer pool). But the writes will eventually happen.
Also, the transaction log is written to (for ROLLBACK or recovery from a crash); this is controlled by innodb_log_file%. (I was asking for the sizes and activity to see if it was too small. I'm not sure of the conclusions.)
All of that adds up to "slow" due to disk I/O. A rule of thumb (for regular disk systems) is 10ms per I/O. If you tally up the index hits, etc, for the 20K queries, you would probably get hours do do all the I/O. So the buffer_pool is helping a lot.
Looking at an other way... If (a big if for data your size) all of the data and indexes for all of the tables could fit in the buffer_pool, then the only I/O would be (1) writes to log, and (2) writes to the data/indexes. Perhaps no reads would happen.
The "first" vs "second" puzzles me -- usually the "first" operation on a 'cold' system has a lot of reads to warm up the cache (buffer_pool). Hence the "first" should much slower. OTOH, the writes are delayed, so they tend not to happen until much later (minutes, hours) when the buffer pool becomes "too dirty".
The EXPLAIN you showed indicates 7 index hits. On a cold system that would be 7 disk hits (actually a lot more, because of drilling down in BTrees). So, I would estimate it at 70ms. Once a lot of stuff is in the buffer pool, that value would drop. Another rule of thumb: Run a query on a cold system, then immediately run it again -- the second run will be 10 times as fast. This is due to the caching. Again, that does not help answer your observation.
I'm missing something to explain what you are seeing. But I don't know what I am missing.