Is it possible to create or simulate a partition-wise join in Mysql/Innodb?? Here is a good description of partition-wise joins....

https://blogs.oracle.com/datawarehousing/entry/partition_wise_joins *A partition wise join is a join between (for simplicity) two tables that are partitioned on the same column with the same partitioning scheme.

If you now join the two tables on that partitioned column you can break up the join in smaller joins exactly along the partitions in the data. Since they are partitioned (grouped) into the same buckets, all values required to do the join live in the equivalent bucket on either sides. No need to talk to anyone else, no need to redistribute data to anyone else... in short, the optimal join method for parallel processing of two large data sets.*


The short answer is no, MySQL does not have that 'feature'.

The long answer (and opinion)...

Perhaps 1% of tables are partitioned. Perhaps 1% of partitioned tables could use that feature. Now imagine the effort to implement, test, and deploy such an obscure feature. Oracle has had several decades to incorporate little-used features like that. MySQL is relatively young. It is still lean. (But obscure features are creeping in. That one has not shown up yet.)

The reason why only 1% of tables are partitioned is that there is no performance to be gained for the other 99%. Usually an appropriate index will do just as good as partitioning.

Would you like to present your use case? (Beware: I may say that you gained nothing by using partitioning.)

  • Thank for your response. The use case is a join between two large tables. This would be on a website where users would expect pages to load in 1-3 seconds. – user5623676 Apr 15 '16 at 20:30
  • I would say that the fact table will eventually have over 100 million rows and the dimension table it is joined to may eventually have 100,000 rows. – user5623676 Apr 15 '16 at 20:39
  • I have seen billion-row tables with 1-second response time. Please share more details; there may be some tricks to speed up your use case. SHOW CREATE TABLE (for both tables) and SELECT ... would be a good start. – Rick James Apr 15 '16 at 21:03
  • I have read about efforts to create tools/platforms that do similar things, over mysql. Haven't seen any results. If I find something, I'll post an answer. As Rick noted, this might be useful only for special purposes / very big dbs, I.e. analytical processing. – ypercubeᵀᴹ Apr 16 '16 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.