Partitions

00:00

I welcome back to modulate This is less than 8.5 partitions. Partitions is another useful tool that can help you optimize your sequel. Database partitions. Divide a single sequel table into multiple, smaller tables. This optimizes performance by providing less data to examine during queries.

00:19

This is hidden from the sequel programming layer or its abstracted.

00:23

So when you program or right sequel against a partition table, you do not have to go through the trouble of targeting the correct partition.

00:31

You will want to create or select a proper partition. Key wind creating a partition

00:37

Failure to implement inappropriate partition may actually her performance. For example, consider a scenario in which you've created a partition for every record on the table that would not be very efficient and would actually yield worst performance if you hadn't partitioned it in the first place. There are different partition types,

00:56

and we'll look at all the types

00:58

in the development environment when we get there. The types include range list, hash and key,

01:04

and the use case for all partitioning is when large tables with millions of records or more

01:11

and the illustration in the bottom right corner, you can see a good representation of what a partition is doing. It's taken the primary table, and it's breaking it up into three parts. Then, when I query has to go to a specific record, it can go specifically to that partition. Now the difference here is they.

01:30

In a real world partition, you wouldn't have three items.

01:33

You would have probably millions or more items. But this is just the service, that visual example. Now, with that, let's head over to the development environment and take a look at how we might implement a partition.

01:46

So here we are in the development environment, have a note at the top for additional information, and it reads as follows. It's a good idea to create the partition when creating the table. When using the altar table method is I have in these examples, the database actually copies all the data into a new area and then discards the older

02:06

so a large table is effectively doubled for a short period of time. So if you have a table that you're going to partition and it has three gigabytes of data for a period of time, it's going to take up six gigabytes of data, and that's something you want to be aware of. If you happen to be tight on space. Also,

02:23

the copying can take a while with a very large table.

02:27

Now, before we go ahead and implement these partitions, let's go ahead and make sure that we have a record that will allow us to see the difference.

02:37

So go to the PM Tran table in Akeem, Attica, and make sure that you have at least one record where the company I d. Is three and not to. And to change that Andy Beaver, it's very simple. You click into it, you change it to a four or two or three or whatever number you would like to use.

02:55

You just click, save,

02:58

and, um

02:59

then you're done.

03:00

So now, if you go back to these partition commands, the first example is by key.

03:07

So again, this is just getting the syntax right. We're going to alter table PM Tran from the axiomatic and database we're gonna partition by key are key is going to be the company I d. And we're going to do partitions number account of to.

03:22

So I'll go ahead and run this.

03:27

Okay, so that took 991 milliseconds, which is a lot slower than a query would take.

03:32

That's because of all that data copying that's going on. That data rearranging that took two milliseconds much faster.

03:39

So if we go to the PM Tran Table and D Beaver, we'll take a look at the properties. We can see that there's two partitions. Make sure you're on the partitions tab

03:50

p zero p one.

03:52

And if we go back to our script and we explain the selection where we're using that key, so let's use where company ID equals three,

04:01

we can see that that's using partition zero or P zero.

04:05

And if we do too,

04:10

we can see this switch the partition one. So we know that we have effectively partitioned the table in the way that we expect. We are able to go to different partitions based on what we're filtering. Four.

04:24

Now let's try with the list example,

04:27

so I could list Mawr in this definition. For example, I could do 34 I don't have a company floor, but I could just make it available. We go ahead and run this

04:43

and thats done

04:44

and again if you go to PM Tran and we refresh it. We'll see the method has changed to list and that we have a description that identifies what's in that list

04:55

and let's go back and run our explain command.

05:00

So the explain where company I DS two would go to partition zero.

05:04

And then if we changed it to three, of course it's gonna go to P one or partition one,

05:11

and that's what we expect.

05:13

Let's test the range example,

05:15

and something else to realize here is that when I add a partition to a table, it's actually replacing the last partition. So I don't have all these partitions on the table. When I run this command, this partition will be replaced with the range example.

05:30

Let me go ahead and run this

05:34

and thats done.

05:35

I go to the PM Tran table and I refresh it again. I'll see that I have a method of range, and I'll have a description that matches that range.

05:46

And then if I run the explain command again,

05:48

I can see that I'm going to pee one for three.

05:53

I switched it to,

05:56

and I'll go to P zero. Just what I expect

05:59

and and the last example we're going to do a altar table by hash with two partitions, which is pretty similar to the key method.

06:11

Now, when the system names the partitions, it starts at P zero p. One. But we could have given them a different name when we define the name ourselves, but I just like to follow the same,

06:21

uh, patterns that I see. So when it's automatically listed is P zero p one. I used that same name and scheme myself in many cases, unless I have a special reason to rename something.

06:33

So let's go ahead and run that altar table are altar table by hash example.

06:40

And that's complete,

06:42

and this is gonna have the expected results for sure. Partition zero.

06:46

Switch to

06:48

three on. We can see that this is going to partition one,

06:53

and then we go to the PM Tran table. We can see that it's changed to a hash method,

06:59

and that is it. With partitioning, there's actually not terribly complicated. Thebe partitioning is just breaking a bigger table into smaller tables to make querying mawr effective.

07:11

Now let's go ahead and head over to the lesson review before we close out this lesson,

07:16

so that brings us to the 8.5 summary. In this lesson, we discussed partitions and partitions divide a single sequel table into multiple, smaller tables. There's different types of partitions. They are pretty easy to implement. It is best to implement when creating the table,

07:33

as the database needs to copy all the data into a new area

07:36

when implementing a partition.

07:40

The use case strengthens as record count climbs, I have heard some indicate they wouldn't implement a partition until they reached millions of records.

07:47

But if you believe partitioning might strengthen your optimization by all means implemented,

07:54

you do want to use this method instead of making your own smaller tables. I have seen some instances in applications where the developer implemented a bunch of smaller tables to how such a large set of data.

08:07

So if you find yourself in that situation, be sure to imply it using partitions instead, it will be easier to manage,