Re: how to speed up for List partitioning table

[Rick James]

1. What do you expect to gain by LIST Partitioning? I have yet to see a use case for it.
2. You have 100*100 partitions scattered across the tables? That is 10K "tables", since each PARTITION is effectively a table. 10K tables has problems in (1) the OS, and (2) table_open_cache.
3. There is overhead in having PARTITIONing; the more partitions, the more overhead. 100 is at the upper limit of practicality.
4. Why reload everything all the time? Could you just provide the 'deltas'?
5. When doing LOAD DATA (or any INSERT), indexes must be updated. Consider loading into a non-partitioned table with minimal number of indexes. If the INSERT..SELECT is reading the whole table, it won't use any index anyway.
6. "Partition pruning" does not apply to LOAD DATA.
7. Let's see "EXPLAIN PARTITIONS SELECT ..." to see if pruning works for it.
8. What version are you running? Some of what I am saying is changing in the latest versions.
9. Please provide SHOW CREATE TABLE for at least one of the tables.

Please elaborate on the goals of the project so we can discuss more efficient designs.