Re: 11 millions records : more than 30 seconds to retrieve simple request

[Rick James]

* 3 hours vs 10 hours -- thanks for the statistic. (10x has been my experience, but there are enough things that can vary, that your 3x is not unexpected.)

* after generation, execute SELECT log_time,milli,value FROM my_table WHERE variable='myvariable' ORDER BY log_time, milli :
How many rows did it return? MyISAM has to work harder because the PRIMARY KEY is not 'clustered'.

* 5 hr OPTIMIZE -- interesting.

* execute again the same SELECT command
See if the "Query cache" is turned on. It probably is. Suggest benchmarking with
SELECT SQL_NO_CACHE ...
Even if that does not explain it, the disk blocks were fetched on the first try, thereby making the second SELECT not have to hit the disk.

* MYISAM needs to be OPTIMIZE to be useable -- No. Or, at least not unless you have a lot of churn (DELETEs, UPDATEs, etc)

* decide to work with INNODB -- Fine. Did you check the disk size? Did you shrink key_buffer_size and raise innodb_buffer_pool_size? More details:
http://mysql.rjweb.org/doc.php/memory

* copy a .IBD file -- Yeah, it won't work. There is something critical in the ibdata1 file.

Consider this: When you are about to remove (archive) table xyz, do
ALTER TABLE xyz ENGINE=MyISAM;
Then you have the .MYD file to copy off, and delete.
Restoring can be done in reverse.
Yeah, it won't be cheap (like the 5 hour OPTIMIZE). Another tip... Remove/add indexes during that same ALTER.
ALTER TABLE xyz DROP (all indexes) ENGINE=MyISAM;
ALTER TABLE xyz ADD (re-establish all indexes) ENGINE=InnoDB;

* including the 'value' field in the PRIMARY key -- That applied ONLY to MyISAM. You don't need it in InnoDB.

* SELECT variable,log_time,milli,value FROM my_table WHERE log_time>='begindate' AND log_time <'enddate' ORDER BY log_time, milli is catastrophic.
With no 'value' field in the primary key: EXPLAIN said 'using where'
With 'value' field in the primary key: EXPLAIN said 'using where,using index' ???
And this is true for both MYISAM and INNODB
-- Ah! that is a subtlety I did not pick on. The reason (in InnoDB) is that the secondary key is effectively
(log_time, milli, variable, value)
The first two fields being what you asked for; the last two being the 'rest of' the PRIMARY KEY.

To get MyISAM to perform as well for that query, add
INDEX(log_time, milli, variable, value)
instead of simply (log_time, milli)