MySQL Forums :: Partitioning :: Is partitioning likely useful for this application


Advanced Search

Is partitioning likely useful for this application
Posted by: Steve Fatula ()
Date: January 13, 2017 05:02PM

Let's say I have a table of products with lots of data in it, 15GB. Just for the sake of argument, say 12,000,000 rows. This table has built up over the years as new products come and go. Products are never removed for a variety of reasons. So, say today half of those are active products, the rest are inactive, and in rare cases, inactive products can be re-activated. Products are activated and inactivated during the day.

Let's further say there this table ends up feeding a variety of systems, each with their own unique data requirements. So, some data might go to system A, some data might go to system B after joining with various other tables, etc.

In the end, the problem has become the time to serial read the table to pick out active items to be processed in 100 or so different ways and purposes. Not that any one particular use by itself is overly slow, but, when you have so many different systems needing the data, each in different ways with many different tables potentially needing joined, it can get lengthy when all added up, and, if there was a simple way to halve this time, that would be a gold mine.

So, do not worry about mixing such queries with online purposes, not the case here. I realize there are many potential ways to tackle this, but for various reasons, most of them are just not feasible. So, let's stay OT here and only deal with partitioning as a potential solution as there is no need to discuss the myriad of other potential ways to deal with it and I am not asking about those.

Would partitioning on a single field, active or inactive flag, likely be a good way to deal with this, i.e., we would then easily read only the active products for 99% of our processes that need only active items. Anything else that also needs inactive products can get those also by reading the entire table. Seems like if 50% of the items are active, then, the time will be around 50% to retrieve those over the table as is.

Is there any potential downside to this method, or, will the performance not likely be as good as it may seem to me?

Options: ReplyQuote


Subject Views Written By Posted
Is partitioning likely useful for this application 181 Steve Fatula 01/13/2017 05:02PM
Re: Is partitioning likely useful for this application 150 Peter Brawley 01/13/2017 06:53PM
Re: Is partitioning likely useful for this application 104 Steve Fatula 01/14/2017 05:47PM
Re: Is partitioning likely useful for this application 105 Peter Brawley 01/14/2017 11:32PM


Sorry, only registered users may post in this forum.

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.