A Deep Dive in How Slow SELECT * is

Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon)
database.husseinnasser.com
In a row-store database engine, rows are stored in units called pages. Each page has a fixed header and contains multiple rows, with each row having a record header followed by its respective columns. When the database fetches a page and places it in the shared buffer pool, we gain access to all rows and columns within that page. So, the question arises: if we have all the columns readily available in memory, why would SELECT * be slow and costly? Is it really as slow as people claim it to be? And if so why is it so? In this post, we will explore these questions and more.
0:00 Intro
1:49 Database Page Layout
5:00 How SELECT Works
10:49 No Index-Only Scans
18:00 Deserialization Cost
21:00 Not All Columns are Inline
28:00 Network Cost
36:00 Client Deserialization
/ how-slow-is-select
Fundamentals of Backend Engineering Design patterns udemy course (link redirects to udemy with coupon)
backend.husseinnasser.com
Fundamentals of Networking for Effective Backends udemy course (link redirects to udemy with coupon)
network.husseinnasser.com
Follow me on Medium
/ membership
Introduction to NGINX (link redirects to udemy with coupon)
nginx.husseinnasser.com
Python on the Backend (link redirects to udemy with coupon)
python.husseinnasser.com
Become a Member on KZbin
/ @hnasr
Buy me a coffee if you liked this
www.buymeacoffee.com/hnasr
Arabic Software Engineering Channel
/ @husseinnasser
🔥 Members Only Content
• Members-only videos
🏭 Backend Engineering Videos in Order
backend.husseinnasser.com
💾 Database Engineering Videos
• Database Engineering
🎙️Listen to the Backend Engineering Podcast
husseinnasser.com/podcast
Gears and tools used on the Channel (affiliates)
🖼️ Slides and Thumbnail Design
Canva
partner.canva.com/c/2766475/6...
Stay Awesome,
Hussein

[@hnasr]

Fundamentals of Database Engineering udemy course (link redirects to udemy with coupon) database.husseinnasser.com

[@Rettou74]

What would be nice is to have some numbers to see the real impact on performance and to know which of this factors are more crucial

[@Epistemer]

Hussein I truly look up to you ❤

[@aaqibhamdule73]

u even look like him 🤣

[@shiewhun1772]

35 seconds in. Noticed the background is less noisy - love that it's just three books. And the lighting is softer. This is nice. I thought the sword was gone till I googled the word Musashi and realized the spirit of the Samurai is still very much here. This is nice too. Back to SELECT * . Hopefully this is the video where it finally sinks in for me what your *fetish* for "SELECT *" is. I'm a big fan, Hussein, I can say you played a big part in my having the career and approach to learning that I have now. But as anybody who's watched numerous videos you've made on databases would notice, you have a thing for SELECT * :)

[@dyto2287]

Bigger problem than it being slow is that it could cause issues with your code when you rollback failed deployments after db migrations because db migrations could add new columns that your older version of the code does not recognise and fail to scan.

[@HarshKapadia]

Kids these day will not call you cringe. They will call you 'an amazing person who teaches us so much and gets us interested in learning the fundamentals'.

[@jswlprtk]

You Sir, are an inspiration for me ❤

[@damjandjordjevic1994]

I enjoy the details you get into.

[@farzadmf]

Thanks for another great video!

[@user-bg5vp5hn2w]

Thanks you for providing such high quality vids 🙏

[@stevez5134]

The man’s a hero

[@bashardlaleh2110]

thank you for sharing these valuable info

[@pnworks9186]

Thank you very much sir. This is a very detailed explanation.

[@codinggavin]

Hussein you are such an inspiration ❤️

[@dariusdoku4320]

Great video sir, you teach the good stuff

[@hunter_-ur5mn]

great and informative watch..

[@ayushpandey1148]

Would love to have the video reg File block and byte. How Read operation is done and its underlying logic

[@mariumbegum7325]

Great video 😀

[@dasten123]

But if you actually need everything, SELECT * is not slower than selecting every column explicitly, right? So SELECT * isn't slow. Selecting in general is slow.

[@davincis1]

You never use * . Unless you want to get hacked and show all nice customer data that should be hidden :) . As well, in many cases you not need all columns as it increases the time you get the data

[@Mohamedrasvi]

This is exactly my thinking. Select * is not slower than selecting explicitly by specifying all columns. The video is talking about selection unnecessary columns.

[@Pranav-pl7eg]

Best CHANNEL

[@eulerpi7042]

Hi Hussein, what you think about ORM? is it worth to used? the pros and cons? any video about it? I would love to watch it thanks :))

[@haythamasalama0]

Great video ✨

[@apoorvgupta2039]

I listen to your shows when going for a walk and damn so much info i gain during that 40 mins.

[@user-ok4fx3kl6f]

Hey Hussein, I have one question- How is select * different from select col1, col2, col3,col4 where id =1? - say I have 100 columns in my DB or some very high number of columns At the end it has to still figure out the page where col1,col2,col3,col4 are , right ? The only part that would be less is- deserialisation cost, n/w cost . But the main part of searching the remaining the columns from heap still lies there even if we are selecting few columns as opposed to *. Is my understanding correct ? can you shed some light ?

[@_dnL]

"A page is.. and a block.. is the most overloaded term in SE." 😅

[@RahulAhire]

I'd love to if you can review Citus postgres distribution

[@hemant_pande]

Hi Hussein, what would be the impact of using an guid as primary key column vs using an autoincrememt as a primary key column?

[@ayaanqui]

I'd like to know this too

[@drpstar]

Read his blog on postgresql vs mysql. There he has mentioned some details on this topic.

[@mtnrabi]

Question: if I do “select *” and in the where clause I put a indexed column - what’s even the benefit of using the index if eventually the db will have to do a table scan?

[@pixaim69]

The dB will do a seek and not a scan.

[@sriteja2510]

Hi Hussein, Even the queries selecting few columns like select a,b,c from t1 where grade>90 still needs to fetch the pages from Disk Randomly how is it greatly different from select *

[@drpstar]

But there is still that unnecessary IO cost of large columns ( text, blob), serialization and de serialization cost, CPU cost for compression and not to mention networking one.

[@saikatduttaece50]

@@drpstar What is we actually need that large column too.

[@satwikburman6841]

The guilt trip whenever I am gonna do a select * from now on 😂

[@BabakKeyvani0]

"Next time you do a 'select * ...' think about the suffering you're causing to all this equipments" 😂😅 26:06

[@abdirahmann]

at this point hussein is a DBA or even better cause he can build scalable backends too, change my mind 😆

[@peppybocan]

Deserialisation is not a problem.

[@mhcbon4606]

title is confusing. Star operator is not the suspect here.. but the deep dive is interesting nevertheless, although, a bit chatty imho.

[@gokukakarot6323]

The videos are kind of good, but man I feel so bad for not being able to sit through this dramatic explanation of things. It’s either my ADHD or just that my grandfather just gets to the point much faster than this. It’s like if buffering had a modern look

[@squirrel1620]

Its slowww... Looking at you Entity Framework, or pretty much any ORM 😅

[@NathanHedglin]

Haha Entity Frameworks Core is MUCH faster.

[@wolfVFXmc]

You can select what data you wanna return from the database in EF core

[@vmarzein]

test