MySQL Forums
Forum List  »  Partitioning

Re: how to speed up for List partitioning table
Posted by: Rick James
Date: December 30, 2014 03:05PM

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.

Options: ReplyQuote

Written By
Re: how to speed up for List partitioning table
December 30, 2014 03:05PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.