Saturday, 8 April 2017

MySQL Partition Pruning

Recently, we learned an expensive lesson about MySQL partition pruning. There, it is better to share it here so that others will not repeat our mistake.


In our system, there is a big stats table that does not have primary key and indexes. This table is partitioned, but the lack of indexes often causes the full partition or even full table scan when query. To make things worse, the system still continues writing to this table, making it slower every day.

To fix performance issue, we want to clean the legacy data and add new indexes. However, this is not easy because the table is too big. Therefore, we chose the long approach by migrating only the wanted data from this old table to a new table with proper schema.

Partition by hash

It would have been fine if we only did what we originally intended to do. However, we changed the partition type for convenient and that made the new table slower.

In the original table, the partition is based on a timestamp column that represents the time as a number of hours from epoch. For example, the first second of the year 2017 in GMT is 1483228800 seconds from epoch. To get the number of hours, we divide the number by 3600 to get 1483228800 div 3600) = 412008.

Because of the partition by range type, we need to have a maintenance script that creates the monthly partition for next year. This way of partition is not very ideal because the partition size is big and not even. Hence, we converted monthly to weekly partition but too lazy to define each range and switched from partition by range to partition by hash.

This is a short version of how hash definition will look like if we do the partition by range
And this is how the partition definition will look like if we do partition by hash
partition by hash (hour_epoch div 168) partitions 157;
The partition by hash type did more than just shorten the syntax. MySQL will try to split records evenly by applying modulo function to select a partition. However, to make the duration of one partition one week, we divide hour_epoch number by 168 to effectively get week_epoch.

With the new table schema, we were happy with smaller partitions, shorter description, and more indexes.

Performance issue

Because of the huge volume of data, we could not fully migrate data to the new schema to verify performance. We only did the preliminary performance test with the data of 2 weeks and did not detect any performance issue. However, in the final testing, we were surprised to observe mixed result. Most of the queries are faster as expected, but some are slower.

After investigating, we realized that instead of scanning only a few partitions, MySQL does the full table scanning for time range query. It is even stranger that this behavior only happens with the date range smaller than 3 weeks. Totally surprised by this result, we overcame our procrastination to read up MySQL document carefully and realize why.

"For tables that are partitioned by HASH or [LINEAR] KEY, partition pruning is also possible in cases in which the WHERE clause uses a simple = relation against a column used in the partitioning expression"

As the document clearly explained, the partition pruning only works with the equal condition for partition by hash type.  However, we did not detect this issue earlier because of the query optimizer will auto convert range condition to equal condition if the number of distinct values in between of the range condition is short enough. Unfortunately, in our early test, the data of 2 weeks is short enough for the query optimizer to hide the problem from us.


After learning about the issue, we struggled to find a way to fix the performance issue. There are 2 proposed solutions

  • Trick the query optimizer to do the work by splitting a big range to multiple small ranges, each fit one partition. In this way, the query optimizer will work on each individual small ranges.
  • Rebuild the schema again with the proper partition type. 
The first solution is quick but dirty while the second solution is too time-consuming. Eventually, we almost decided to launch the new table with the first solution until finding a quick way to implement the second solution.

We have dug through MySQL document and learned that re-parititioning is basically a copy and paste operation. However, MySQL also has another command that allows us to do some partition change without too much effort.
    WITH TABLE nt;

In this command, MySQL allows us to exchange partition between a table and a partition of another table. Even when this is not a direct exchange between 2 partitions of 2 tables, it is just a matter of inconvenience to do one more middle swap to a temp table.

This is how our partition swapping looks like


Even though this is not as fast as you may guess as MySQL will do a row by row validation to ensure every record of temp table is elligible for storing in the final table partition. If we use MySQL 5.7, this validation can be turned off by adding "WITHOUT VALIDATION" to the end of the second command.

Because we use Aurora, which only support MySQl 5.6, it still took us 2 days to fully update the partition type. However, this would have been one month if we do not use partition exchange.

Fortunately, we managed to recover from the mistake this time. We hope that you learn from our mistake and do remember to read the document carefully before using any fancy method.


  1. Thank you for sharing your thoughts and knowledge on this topic. This is really helpful and informative, as this gave me more insight to create more ideas and solutions for my plan. I would love to see more updates from you.

    Web Hosting Services

  2. Practice all that you can. Football may look easy play bazaar satta king when watching it on television, but that's far from the truth. It's a very physically demanding sport that also take a lot of brain power. You need to remember patterns and think on your feet with little notice to succeed. All of this takes bazaar satta king


  3. I am very amazed by the information of this blog and i am glad i had a look over the blog. thank you so much for sharing such great information
    clipping path service|Photo Retouching services|Vector Tracing

  4. Hello Admin!

    Thanks for the post. It was very interesting and meaningful. I really appreciate it! Keep updating stuffs like this. If you are looking for the Advertising Agency in Chennai / Printing in Chennai , Visit us now..

  5. United States of America commonly known as US. US holding 50 states and the all states having own value in every perspectives , If you are planing for the visit as a tourist in USA. Then you have many options to visit USA. If you want to know where to visit people mostly? Then you can see by these give below pictures. Most of the people visit to these destinations and love it.
    Cheap Flights
    For Cheap Flights
    Cheap Flights to Florida
    Cheap Flights to Miami
    Cheap Flights to Amsterdam
    Cheap Flights to Wichita
    Cheap Flights to Moscow
    Cheap Flights to Denver
    Cheap Flights to Atlanta
    Cheap Flights to Buenos Aires
    Cheap Flights to Boston
    Cheap Flights to Chicago
    Cheap Flights to Sao Paulo
    Cheap Flights to Thailand
    Cheap Flights to Las Vegas
    Cheap Flights to San Francisco
    Cheap Flights to Seattle
    Cheap Flights to Los Angeles

  6. satta king record chart result galiis an amazingly pleasant game, and furthermore is basically innocuous.
    It offers happiness, pleasure, energy, amusement and furthermore makes individuals tycoons and furthermore extremely rich people
    satta king record chart result gali

  7. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    court marriage in delhi ncr
    court marriage in delhi
    court marriage in noida
    court marriage in ghaziabad
    court marriage in gurgaon
    court marriage in faridabad
    court marriage in greater noida
    name change online
    court marriage in chandigarh
    court marriage in bangalore

  8. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...

  9. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    birth certificate in delhi
    name add in birth certificate
    birth certificate in gurgaon
    birth certificate correction
    birth certificate in noida
    birth certificate
    birth certificate in ghaziabad
    birth certificate in india
    birth certificate apply online
    birth certificate in bengaluru


  10. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    death certificate in delhi
    death certificate in bangalore
    death certificate in mumbai
    death certificate in faridabad
    death certificate in gurgaon
    death certificate in noida
    duplicate driving licence delhi
    death certificate online
    death certificate
    death certificate apply online

  11. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    name change procedure in ghaziabad
    name change procedure delhi
    name change procedure gurgaon
    name change in faridabad
    name change in noida
    name change
    name change in india
    name change procedure in bangalore
    name change procedure in rajasthan
    name change procedure in maharashtra

  12. best site for satta king result, leak number  all game record charts. We provide 100% fix number direct from Satta king gali company which includes all famous games like Satta king Desawar, Gali Satta, Ghaziabad, Faridabad, Shri Ganesh Satta, Taj Satta King, charminar and other games of Satta Market Matka is also a simple game and essentially is a form of old lottery games. Ratan Khatri was the founder of this game in the 70 century and was become popular up until the 90 century. The game is not played that much anymore mostly in the regions of North India and Pakistan. Instead, many enjoy the lottery games Satta king result  more so these days.
    Here is an example card. is the no1 satta king site where you can get the fastest Satta result, Satta king leak number (confirm jodi), Old Satta King ghaziabad, Daily leak Jodi, Desawar Jodi, Satta king faridabad, Satta record chart, Satta king taj, Gali Satta result, Ghaziabad Satta Result, Satta Bazar result and 100% passing fix Jodi today.

  13. Informative and educative post you have shared with us . A lot of thanks for this posting . Image Masking Service | Cut Out Photo | Photo Cut Out

  14. brother scanner

    brother control center

    fix brother printer offline

    setup brother printer wifi

    why is my brother printer not connecting to my mac very nice… i really like your blog…

    very interesting , good job and thanks for sharing such a good blog.

    Interesting stuff to read. Keep it up.

    This blog is great check it out:

    Really very happy to say,your post is very interesting to read.I never stop myself to say something about it.You’re doing a great job.Keep it up

    I am really happy to say it’s an interesting post to read . I learn new information from your article , you are doing a great job . Keep it up

  15. Students find Nursing Assignment Writing Services as being of great assistance since they are able to seek our custom nursing research writing services and nursing homework assignment help services on time.

  16. There is a reliable Healthcare Assignment Writing Service that is very popular for students in search of healthcare coursework writing services and nursing essay writing help services.

  17. The ultimate goal of entrepreneurship coursework writing services is to provide Entrepreneurship Writing Services and entrepreneurship essay writing services since entrepreneurship assignments writing service seekers lack time to complete their entrepreneurship term paper writing services.

  18. great java tips At SynergisticIT we offer the best java bootcamp in bay area

  19. Nice and interesting post. Thank You! For sharing such a great article, I like to read your information you have mentioned in this article are helpful for me. for More Information Click Here: Outdoor Advertising Company in Jaipur

  20. • You must first navigate to on a computer browser, phone, or tablet.
    • Next, key in the link code from the TV in the prompted section and click on the “Submit” button.
    • Secondly, you must sign in to your Roku account.
    • If not having an account, ensure that you create one.
    • Moreover, finish the set up by offering all the needed details and choosing the channels to add to the streaming device.
    • At last, once after completing the online set-up, the Roku screen will update, and the selected channels will be installed on the streaming device.

  21. What is the Roku Activation code?
    Roku activation code is one that is mandatory to activate the Roku device. To get the code, you need to complete the initial steps on the Roku device. Only after this, you can get the code on the Roku device. For the activation, you can surf to the Roku activation page and enter the code on the site to activate the device. After the device activation to launch the channels on the Roku device, you need to sign in the Roku account to the Roku If you want to activate roku through Roku activation code you may visit our site

  22. The logistics performance index of Finland is 3.62. It indicates a satisfactory performance - in general, traffic is handeled well, some flaws in certain areas are possible, but overall the logistics system performs reliably and is ready to handle predictable amounts of traffic.

  23. I suppose that is an informative claim and it's far rather beneficial and knowledgeable. consequently, i might finally to thank you for the undertakings that you have made in writing this text. all of the content is truly adeptly-researched. thanks.

    Read More:- Satta King

  24. Lets make it easy for you to get all info about Satta King. Its a game or you can say lottery where you can win lakhs or crore of rupees by investing very small amount of money . Many people are winning more than 5 lakh - 50 lakh from this SattaKing . Some are loosing money also.

  25. I think this is a really good post. You make this information interesting and engaging. You give readers a lot to think about and I appreciate that kind of writing.

    Read More:- Satta King

  26. Satta King
    "This is the best blog with all the related information which helps us alot and yes I would also thank to the author for sharing such a wonderful and most impotently the helping information.
    Thank you."
    If Want Play online Satta King Game Click Satta King :-

  27. Find Hyderabad Call Girls Escort Service Independent Call Girls in Hyderabad. The largest call girls ads selection in Hyderabad. Browse in our call girl category for finding a Sex Meetiing in Hyderabad. Call girl number whatsapp in Hyderabad

    Call Girls in Hyderabad
    Call Girl in Chandigarh

    Call Girl in Chandigarh
    Call Girls in Hyderabad

  28. Excellent post! Your post is very useful and I felt quite interesting reading it. Expecting more post like this. Thanks for posting such a good post. laptop service in home. To service your laptop with offer prices, Please visit : Laptop service center in Navalur

  29. The way you describe this blog is really nice. Thanks Shearing I recommend you visit my site which gives you free online typing speed test and exciting typing games and keyboarding practice. Check your WPM for free now! With us here : typingspeedtest

  30. sites that provide the updates you want, please visit now. prediksi togel

  31. sites that provide the updates you want, please visit now.

    prediksi togel korea hari ini

  32. Permain Di Togel Ini Banyak Sekali Mengundang Banyak Pemain Judi Toto Online Yang dinamai Dengan Permainan Toto atau togel Yang Dimana Permainan Ini Merupakan Salah Satu Permainan Togel Terbaik Di Indonesia
    live draw sgp
    live draw sgp tercepat

  33. click on one of the sites below to get a variety of the best tips and tricks in life.

    paito warna pcso

  34. Nice And Awesome Post! So Thanks for Sharing This Information Helpful For Me. If also daily check Satta king Results. And Satta King Gali, Deshawar, Faridabad, Ghaziabad and other sattaking Result.

    Hai Teman - Teman Smuanya, Yang Hobi Bermain Judi Online
    Anda Merasa Capek, DEPOSIT Teruss..Tapi Tidak Pernah Withdraw ?
    Nikmati Seluruh Game Berkualitas & Raih Kemenangan Anda Di SAHABAT KARTU!!
    Game Yang di Hadirkan SAHABAT KARTU Yaitu :
    * Poker Online
    * BandarQ
    * Domino99
    * Bandar Sakong
    * Sakong
    * BANDAR66
    * AduQ
    * Sakong
    * Perang Baccarat
    * Perang Dadu
    - Minimal DP & WD Cuma Rp. 20.000
    - BONUS CASHBACK 0.5% ( Setiap Hari Senin )
    - REFERRAL 20% ( Seumur Hidup )
    * Livechat : SAHABAT KARTU
    * LINE : Cs_sahabatkartu
    * Whatsapp : +855886427216

    Sahabatkartu: Situs Poker Online, DominoQQ, Domino99, BandarQ Terpercaya

  36. Merupakan Salah Satu Situs yang terpercaya Di Indonesia
    dominowin99 Memiliki Permainan Yang Mudah Dimainkan & 100% Mudah Menang Lohh..
    Cukup Dengan 1 USER ID Anda Bisa Bermain 10 GAME Berkualitas :
    * Poker
    * Domino99
    * AduQ
    * Capsa Susun
    * Sakong
    * Bandar Poker
    * Perang Baccarat
    * Perang Dadu ( New Game )
    - sdomino99net

    < Contact Us >
    Info Lebih Lanjut Hubungi :
    W.A : +6285974599065

    Sahabatdomino : Situs QQ Online, Agen Domino99 dan BandarQ Online Terbesar Di Asia

  37. Company formation in Denmark involves a number of stages of varying complexity and therefore requires professional assistance. The Confidus Solutions team will prepare legal documents, assist with registration and finalise the incorporation procedure.

  38. click on one of the sites below to get a variety of the best tips and tricks in life.

    master togel88

  39. click on one of the sites below to get a variety of the best tips and tricks in life.

    data pengeluaran togel

  40. click on one of the sites below to get a variety of the best tips and tricks in life master togel88

  41. Enjoyed reading the article above , really explains everything in detail,the article is very interesting and effective.Thank you and good luck for the upcoming articles
    To learn more about How to write fast without lowering quality of content. Please do refer the article fast writing guide. Here you will learn more. Thank for the attention.

  42. Very interesting, good job and thanks for sharing such a good blog. Your article is so convincing that I never stop myself to say something about it. You’re doing a great job.Keep it up.
    This is the article where you will learn about Best Summer Camp Activities For Parents To Make. So if you want to learn more about this you can go through the full article name outdoor camping activities for kids. Visit and be a tech-knowie.

  43. It was really an interesting time reading this article, we appreciate every article you've made. This is the website where you can get to learn more Text to Speech advantages and disadvantages. Here you can get all tips an tricks related to text to speech .So please visit the site to read it's latest post text to speech converter.

  44. It was really an interesting time reading this article, we appreciate every article you've made. This is the website where you can get to learn more the greatest cloud-based gaming. Here you can get all tips an tricks related to gaming. So please visit the site to read it's latest post RBX codes.

  45. It was really an interesting time reading this article, we appreciate every article you've made. This is the website where you can get to learn more Test typing speed. Here you can get all tips an tricks related to typing. So please visit the site to read it's latest post Typing speed.

  46. This comment has been removed by the author.

  47. Read the article above. It's very informative and clear, it explains everything in depth. Thanks and best wishes on your upcoming articles The article on Mouse Left Click Not Working can provide more information about quick fix for mouse left click issue. You can learn more here. I appreciate your attention.

  48. We tell you about Income Tax Return.

    This tax in India since 1961 through the multiple Amendment of the Constitution of India.

    This article is really helpful to you, Every business and offices required Income Tax Return in Delhi and Income Tax Return in Faridabad. We also provide professional service for tax return, tax guidanace in Income Tax Return in Karnataka as well as we provide Income Tax Return in Ahmedabad and Income Tax Return in Kerala.

    Get complete detail about income tax, tax refund status, income tax filing procedure, pan number, tax guide. Tax experts in India provided by TaxWala will assist you through the entire process. Online Income Tax Return File your return application & get your acknowledgement Online. Agents and consultanst at TaxWala help you to file income tax return done online in 3 hours without any problem.

    Our Tax Consultants also available for Income Tax Return in Gujarat, Income Tax Return in Haryana, Income Tax Return in Rajasthan and Income Tax Return in Punjab.

    We are best in taxation services, itr filing, income tax return in india etc.

    For SEO, SEM - Top 10 Digital Marketing Company In India

  49. Your blog writing skills contains heights of creativity, effort and hard work. I guess almost every visitor impressed with information shared here. Thanks for publishing it for us. help with programming assignment

  50. Very interesting, good job and thanks for sharing such a good blog. Your article is so convincing that I never stop myself to say something about it. You’re doing a great job.Keep it up.
    This is the article where you will learn about fast auto clicker for mac So if you want to learn more about this you can go through the full article name Auto Clicker for Mac. Visit and be a tech-knowie.

  51. I think this post is very informative and helpful. I have to add this to my collection. You did a great job! Very interesting story. Based on your previous post Keyboard to type fast, I also wrote an in-depth article. You may be interested in reading this article Fast Typing Keyboard. Thanks for visiting.

  52. I have read a lot of blogs and articles but your information is so helpful to me. Your work is really appreciated. Continuing your work and sharing this kind of information will be greatly appreciated. Also, I have a blog related to Voiceover Words Per Minute in which I have provided a lot of information. Check it out (making words per minute with your voiceover selection and let me know what you think.

  53. Thanks for provide great informatic and looking beautiful blog, really nice required information & the things i never imagined and i would request, wright more blog and blog post like that for us. Thanks you once agian

    Best Accounts Institute In Sirsa

  54. Thank you for posting such a great article. Keep it up mate.

    Gati Shakti Yojana 2021

  55. Nice Blog , This is what I exactly Looking for , Keep sharing more blog .
    PHP framework Developers in India

  56. Thanks for the information there are all wonderful and interesting. Thanks for sharing with us your ideas. is fudutsinma post utme form out

  57. 5 bandar togel terpercaya saat ini di media seperti internet luas sudah banyak berkembang dengan hadirnya beragam macam permainan.

  58. bandar togel terpercaya yang sedang digemari masyarakat luas terdapat permainan seperti togel, slot, dll yang pastinya mudah di akses.

  59. data hongkong terbaru yang sudah disusun dan dirangkum ke dalam bentuk tabel agar lebih mudah dilihat.

  60. live draw hk yang sedang banyak dicari oleh para pecinta hongkongpools.

  61. Era Internet membuat para pecinta togel dengan sangat mudah mendapatkan informasi seputar DATA HK yang sangat penting.

  62. Setiap harinya kunjungi blog DATA HK untuk mendapatkan pengetahuan lengkap seputar pengeluaran angka togel hongkong.

  63. Situs BANDAR TOGEL paling aman dan terpercaya di Indonesia dengan berbagai macam pasaran dan hadiah togel tertinggi.

  64. LIVE DRAW SYDNEY merupakan situs pengeluaran angka togel sydney terbaik dan tercepat setiap hari untuk membantu para pecinta togel Indonesia

  65. Team terbaik dan paling layak untuk memberikan rekomendasi BANDAR TOGEL TERPERCAYA di Indonesia yang sudah kami pastikan valid.

  66. Pusat rekomendasi AGEN TOGEL TERPERCAYA Indonesia yang di rekap dari tahun ke tahun sehingga menjadikan suatu kesatuan yang valid.

  67. Dapatkan informasi seputar rekomendasi BANDAR TOGEL TERPERCAYA aman dengan system paling bonafit menguntungkan di Indonesia

  68. AGEN TOGEL TERPERCAYA - situs rekomendasi kumpulan agne togel terpercaya yang dijamin membayar semua kemenangan member berapapun nominalnya

  69. Link menuju BANDAR ONLINE terbaik dan terpercaya yang kami kumpulkan dalam 1 tempat agar mudah di akses.

  70. Kata kunci SITUS TOGEL TERPERCAYA merupakan hal yang sangat penting di mesin pencarian dan populer, oleh karena itu kami membuat situsnya agar kalian dapat menemukan semuanya dengan mudah.

  71. BANDAR TOGEL aman dan terpercaya terbesar dengan hadiah togel tertinggi dan bonus terbanyak yang dijamin membayar semua kemenangan member berapapun nominalnya.

  72. LIVE DRAW HK ialah situs penyiaran langsung togel hongkong yang di update setiap hari tercepat dan terlengkap.

  73. Setiap hari kami berikan untuk anda BANDAR TOGEL terpercaya yang sudah kami rangkum dengan sangat teliti.

  74. Portal digital LIVE DRAW HK terbaik nomor 1 di Indonesia yang di dirikan dengan tujuan membantu para pecinta Togel Indonesia.

  75. LIVE DRAW SDY bekerja sebagai penyiar pengeluaran nomor togel sydney langsung setiap hari tercepat nomor 1 di Indonesia

  76. DATA SDY merekap hasil pengeluaran togel sydney setiap hari dengan tampilan sederhana untuk mempermudah visitor mendapatkan informasi

  77. DATA SYDNEY memberikan laporan rekapan hasil result prize 1 togel sydney setiap hari tercepat dan terlengkap

  78. Di kalangan para pecinta Togel Indonesia, LIVE DRAW SDY merupakan salah satu hal yang penting yang dapat memberikan hiburan dalam bermain tebak angka tersebut.

  79. DATA SGP - Portal rekapan pengeluaran angka togel singapore setiap hari tercepat dan terlengkap dari tahun 2019 - 2021. Situs akan di update setiap hari untuk membantu para pecinta Togel Indonesia

  80. LIVE DRAW HK merupakan blog penyiaran langsung togel hongkong tercepat yang di update setiap hari

  81. Dapatkan hasil pengeluaran angka togel sydney prize 1 Indonesia yang biasa di sebut dengan : DATA SDY di blog kami hanya dengan 1x klik.

  82. Para pecinta togel Indonesia sangat gemar mencari DATA SGP di Internet dengan mengetikan kata kuncinya dengan mesin pencarian.

  83. Angka togel keluaran sydney merupakan angka yang sangat di cari dan di butuhkan bagi para pecinta Togel Indonesia, maka dari itu team kami membuat suatu blog yang memperbaharui keluaran togel sydney setiap hari bagi anda yang kami sebut dengan : DATA SYDNEY

  84. Bagi para pecinta togel hongkong, kami sarankan hanya menyaksikan pengeluaran angka togel LIVE DRAW HK setiap hari di situs yang kami rekomendasikan. Mengapa demikian, karena situs yang kami rekomendasikan merupakan situs yang paling cepat dan valid dalam memperbaharui angka setiap harinya.