In this video, I explain how both Postgres and MySQL store their indexes and their effect on reads vs writes. Let us discuss 0:00 Intro 1:00 Tables 2:00 Indexes …
Tag: postgres vs mysql, [vid_tags]
Xem thêm: https://meohay360.com/category/internet
Nguồn: https://meohay360.com
Internet
Indexing in PostgreSQL vs MySQL
21
Th3
Th3
I suggest keeping a conclusion or summary at the end of the video for those who dont want to see the whole video. Great content btw.
Hi Hussein, 7:22, you said that in mysql, if we delete a row, only the primary key needs to be updated, other secondary indexes are not aware of this removal. Does this mean there is dangling/redundant information in the secondary indexes (column values no longer exist after deleting the row but still exist in their corresponding indexes)? How does the db engine handle this? Is there any documentation/source I can read? Appreciate your help 🙂 [for the context i have completed your db course, but struggling to understand this indexing part]
WOW! I don't understand why this video has so little views and likes, I have just subscribed to your channel. Very nice explanation, even without any diagrams and charts you did great!! Love from Bangladesh!
OMG, you explain things ao well. Just one thing, please exaplain with any simple table and drawing.
speak a bit faster, even at 2x it's too slow for me…
Test, test, test. Any decision must be based on valid and realistic information.
Great job explaining the difference!
Thank you for your videos
I guess this is coming from the Ubers move from PG to MySQL 🙂 Your videos are gold ! making a habbit to watch them everyday !
How about non-indexed but variable-length columns like TEXT? If the text values are changed, don't all offsets in the table after the updated row have to be updated in the index, basically +delta bytes?
Or such columns use some other tricks, e.g. Fixed-width pointers to the actual string? But then, where are the strings kept?
I could imagine a lot of tricks, both with advantages and disadvantages, but basically the question is: how is data organized in a table for both innodb and postgres?
If you delete a row in MySQL, the indexes are not updated? Are they updated later during some kind of cleanup? They still contain pointers to a primary key value that does not exist.
You misunderstood innodb layout. Innodb tables are IOT (index organized table). MEANING that all table data is stored in primary key!!!
عظمة ♥️
Hussain, Indepth Intro to FaunaDB plz, reason It's an Document based DB with ACID compliment on all the Clusters and some graph based too
Hussein , if there is an update/deletion on an indexed column , then only the Btree (for that indexed column )would be rebalanced right ? and this would be true for both postgres as well as mysql . updation/deletion on a non indexed column wouldn't rebalance the Btree right ? , so why do they use different (index) pointer mechanism ?
Good video … however lots and lots of annoying ads
I like that you summarize the information in your videos and your style seems like mine. Thanks man… keep pushing this channel!
Hi Hussein, I found your channel yesterday and it is just gold. Thanks a lot for the amazing content.
thanks for this great content
Hussein, two doubts.
1). In innodb if we delete a row from table, primary index has to be updated ( actually one entry would be deleted), then all the other indexes pointing to primary index should also be updated otherwise they'll be pointing to a "no longer existing" memory location. If it'll happen then there is no difference in innodb and postgres in case of deletion.
2). When update operation is performed on a column which is a secondary index in innodb, then it is not necessary that primary index will be updated (because it might not have that column as part of primary index), only secondary index(s) will be updated which has that column. But again it will be same as postgres because there also all the indexes which has that column, will be modified.
If both of the above mentioned points are valid then innodb indexes are same as postgres indexes in case of update and delete, and for read operation innodb indexes would be little bit slow because of extra hop as you have mentioned.
Please correct me if I'm not getting it correctly.
Thanks for this video Hussein 😊
Hey, can you make a tutorial on how to connect node.js PostgreSQL with digitalocean database because I'm getting weird errors about self certificates and bg_hba.conf
7:24 so basically InnoDB is good when you have multiple indexes in a table that is being frequently changed – deletes are being performed frequenty. So that in order not to update all the indexes only the primary key is being updated…
Edit: Also it means you should keep the number of indexes to a minim – only columns that you know will be searched frequently.
Would be interesting to know how are indexes managed in other non transactional mysql engines such as myisam
Awesome content 👍
Hussein, thanks to you I've got multiple job offers. I cannot stress this enough how much you've helped me. Software engineering is all about a certain way to think, to see problems from a certain perspective and I learnt it from you. Once again, thanks a lot 🙂
Just one question.. How many books you completed in behind shelves?
Very educational, I think the volume was too low, I'm not sure if it's a compression issue but anyway thanks for the information.
When using Postgres, many times I have encounter the error of trying to insert a new row with a duplicated primary ID. (Which this id is generated by Postgres) The table would always get out of sync after deleting some rows. So to fix, I would have to manually count how many rows and afterwards insert +2 or something. Could this be because of the “row has to know about every row mechanism” & somehow would glitch & get out of sync? I don’t know….
do you have some content for , how to scale backend server with some live example ? it would be very helpful.
Good day to you Hussein -sensei 😀
Suggestion: Show a PP presentation for these kinds of videos. Thanks for your content. 🙂
Plz anyone answer the below question
Sir I have a doubt
Why no one is creating a operating system after Microsoft and apple
Even if they do it why it is not being a huge hit