1 00:00:00,060 --> 00:00:03,460 ‫So what is the pros and cons of partitioning, is this thing perfect? 2 00:00:03,480 --> 00:00:04,770 ‫Obviously, nothing is perfect. 3 00:00:04,780 --> 00:00:09,060 ‫There's always everything is a double edged sword when it comes to back in engineering. 4 00:00:09,180 --> 00:00:11,990 ‫And we need to understand this thing as back in engineers. 5 00:00:12,180 --> 00:00:12,420 ‫Right. 6 00:00:12,660 --> 00:00:17,620 ‫So the pros of partitioning is beautiful because it improves the query performance. 7 00:00:17,640 --> 00:00:23,270 ‫Yeah, we didn't really notice as much of my demo because I'm using a laptop and I want to docker container 8 00:00:23,280 --> 00:00:33,660 ‫but improve and increase that the size of the tables to the billions and make so that your memory, 9 00:00:34,320 --> 00:00:40,560 ‫your memory bound or ironbound, because I wasn't even remotely born or eyob on my table in my case. 10 00:00:40,560 --> 00:00:40,740 ‫Right. 11 00:00:40,740 --> 00:00:43,910 ‫Because I had I had this loss of memory to work with. 12 00:00:44,280 --> 00:00:49,500 ‫But if you are limited with the memory, you can start seeing it because you're going to pay to disk 13 00:00:49,650 --> 00:00:56,220 ‫and you're going to hit Io's and you start feeling the query versus working with a huge table. 14 00:00:56,220 --> 00:00:56,430 ‫Right. 15 00:00:56,730 --> 00:01:01,950 ‫So a performance when working with a single partition or a little bit fewer partitions. 16 00:01:01,950 --> 00:01:08,010 ‫It's a little bit way better, actually, than working with a huge single table. 17 00:01:08,110 --> 00:01:11,310 ‫And so working with a smaller partition is always better. 18 00:01:11,430 --> 00:01:16,470 ‫So as long as I know how to hit that partition, that's the key here. 19 00:01:17,400 --> 00:01:23,510 ‫So sometimes the database decide on a sequential scan versus a scattered index scan. 20 00:01:23,580 --> 00:01:26,740 ‫So, guys, the database always makes decisions. 21 00:01:26,820 --> 00:01:27,900 ‫This is called the planner. 22 00:01:28,380 --> 00:01:28,920 ‫The Planner. 23 00:01:28,920 --> 00:01:32,550 ‫The David says, OK, you are Sinemet query and there is an index on the table. 24 00:01:32,700 --> 00:01:34,830 ‫But I don't have to use the index. 25 00:01:34,830 --> 00:01:42,060 ‫If if you're going to pull every single rose in the table, it's a waste to head the index, actually, 26 00:01:42,300 --> 00:01:47,430 ‫because going to the index, you're going to start jumping through an index and going back to the table 27 00:01:47,430 --> 00:01:49,770 ‫and going back to the index and then go back to the table. 28 00:01:49,770 --> 00:01:53,280 ‫That's actually slower sometimes to head the index. 29 00:01:53,520 --> 00:01:53,940 ‫Right. 30 00:01:54,270 --> 00:01:56,070 ‫And it's called scatter index scan. 31 00:01:56,100 --> 00:01:56,970 ‫That's slower. 32 00:01:57,150 --> 00:01:58,260 ‫So those are the database. 33 00:01:58,260 --> 00:02:03,660 ‫Decide to hit a sequential scan on the table directly says, you know what, it's not worth it to you 34 00:02:03,660 --> 00:02:07,950 ‫as the index for that particular query, because I'm going to select lots of rows. 35 00:02:08,070 --> 00:02:12,030 ‫And that's a complicated logic, fuzzy logic by the database. 36 00:02:12,870 --> 00:02:19,800 ‫And that includes a lot of EHI, if you think about as well, to to actually decide what to do. 37 00:02:20,580 --> 00:02:28,530 ‫So some based on that, a partition by giving the partition is actually smaller right now. 38 00:02:28,530 --> 00:02:33,240 ‫It actually can make this decision easier because, hey, psychological screening is actually better 39 00:02:33,360 --> 00:02:36,510 ‫in this case, then a scatter index card on a huge table. 40 00:02:36,720 --> 00:02:41,760 ‫So deciding which one is you just made the life of database easier using partitions. 41 00:02:42,150 --> 00:02:45,710 ‫Beautiful, beautiful, easy bulk loading. 42 00:02:45,720 --> 00:02:52,770 ‫So if you have, you can essentially just create a table and attach it to the partition immediately, 43 00:02:53,010 --> 00:02:57,330 ‫right to the partition table without without worrying about anything. 44 00:02:57,330 --> 00:03:05,460 ‫So you can bulk data load in into a given table and then attach that table to an existing partition 45 00:03:05,460 --> 00:03:12,810 ‫table, given that actually the constraint and the checks are all valid and all that stuff. 46 00:03:12,810 --> 00:03:12,930 ‫Right. 47 00:03:12,960 --> 00:03:15,900 ‫You can just attach of invalid partition. 48 00:03:15,900 --> 00:03:16,140 ‫Right. 49 00:03:17,010 --> 00:03:18,990 ‫So this is this is a very popular thing. 50 00:03:19,320 --> 00:03:27,420 ‫It's really in my school you can actually have a CSV file and create the storage engine because that's 51 00:03:27,420 --> 00:03:28,170 ‫only available. 52 00:03:28,650 --> 00:03:36,180 ‫And MySQL and morality by belief have the CEV be the storage engine for a given table. 53 00:03:36,180 --> 00:03:39,240 ‫And just like that, you have a table magically. 54 00:03:39,240 --> 00:03:46,050 ‫If you have like a a three hundred million servers, you point your table to that CEV and you have a 55 00:03:46,050 --> 00:03:47,760 ‫table that you can query immediately. 56 00:03:47,940 --> 00:03:48,280 ‫Right. 57 00:03:48,450 --> 00:03:50,490 ‫And then you can use that to attach it. 58 00:03:50,500 --> 00:03:56,220 ‫It's a little bit complicated, but that's one benefit of using MySQL over Polska, for example. 59 00:03:56,350 --> 00:04:03,200 ‫Obviously, that doesn't mean MySQL is better than Paul's because that's just one use case archive. 60 00:04:03,240 --> 00:04:05,910 ‫All data that are barely accessed into storage. 61 00:04:05,910 --> 00:04:06,140 ‫Right. 62 00:04:07,260 --> 00:04:13,410 ‫Two thousand one data that partition is no longer used. 63 00:04:13,410 --> 00:04:14,760 ‫You have barely queried. 64 00:04:14,760 --> 00:04:16,680 ‫And you can check this thing real quick. 65 00:04:16,890 --> 00:04:18,170 ‫That's actually pretty neat, right? 66 00:04:18,480 --> 00:04:20,730 ‫This partition table is barely query. 67 00:04:20,850 --> 00:04:29,310 ‫So let's throw it in a table space so that that nobody is using on a drive that is pointing to an old 68 00:04:30,030 --> 00:04:33,060 ‫hard disk that is slow and cheap. 69 00:04:33,060 --> 00:04:33,360 ‫Right. 70 00:04:33,510 --> 00:04:40,980 ‫And let's use this precious our precious SSD and beautiful, beautiful hour fast, frequently access 71 00:04:40,980 --> 00:04:41,310 ‫data.