Re: Slow query on partitioned tables with over 200M rows
Posted by:
Rick James
Date: February 12, 2014 08:47AM
> Each partition of the table contains over 200M rows.
> raw.fct_ts >= "2014-02-10 00:00:00" AND raw.fct_ts < "2014-02-11 00:00:00"
It takes a long time to scan 200 million rows.
> '1', 'SIMPLE', 'raw', 'p735639', 'range', 'PRIMARY', 'PRIMARY', '12', NULL, '229101935', 'Using where; Using index'
Says
* It did do the partition pruning as expected.
* It used all three fields ("12") of the PRIMARY KEY
* It used the index
The last two cluse are bogus, since it really just did a table scan of that one partition. But there is no better way to hit all 200M rows.
But there is another issue... You are asking for a resultset of 200M rows. And, by default, you are asking for all the rows to be delivered at once.
Which interface are you using? Look around for the option that lets you get the rows as they are found. (This may be the non-"quick" mode.) Setting this option (perhaps in the OPEN) should give you data almost immediately, but will run and run until it has delivered all 200M rows.
The normal (quick) mode _finishes_ sooner and releases locks more quickly.
You have a buffer_pool of 100G; I hope you have more RAM than that.
One minor improvement that might be possible (but very painful considering the size of the data): INT takes 4 bytes; DOUBLE takes 8 bytes. Would MEDIUMINT/FLOAT/etc be sufficient? (Smaller -> less I/O -> faster.)