From 2.5 million row reads to 1 (optimizing my database performance) 

You've become my favorite web dev content creator in no time, no fancy intros and straight to the point with key subjects.

Please do more performance content, this was really helpful!

I knew about all of these but this was a good watch anyway. Your videos are very informative and not very long so people can actually learn something quickly.

lots of great advices throughout the video, awesome vid !

performance tuning content is really helpful. thank you!

When you index a column in a table (doesn't matter if its thru prisma) it creates a b-tree on a disk (balanced binary search tree), rule of thumb is to not index a lot of your columns in the table and try to know your access patterns on data beforehand (or just access data using primary keys which are already indexed by default). If you start stacking up indexes on everything, your write performance will be terrible overtime and you will lock out tables, because every index has to overwrite several pages in the multiple trees. If you still plan to run analytical queries on your OLTP database (you shouldn't) at least create read replica and index columns that you want to query there, by doing that you will get eventually consistent storage (considering replication lag).

At my job I had to create a data warehouse that stores so much data and gets frequent queries so I had to learn all about clustered and non-clustered indices. Good stuff!

Great video! I was just thinking about this topic yesterday. I’m using planet scale as well so the video was very helpful. Also, I found icon gen quite awesome. I purchased credits and was able to gen some kick ass icons for my app. It definitely boosted the user experience and gave it the missing flavor I needed.

You are awesome tbh you deserver millions of subscribers !!

I'm happy to see how much people vists your website

I’d love to see a series of performance optimizations for SaaS ❤ Amazing one bro 🤜🏽🤛🏻

I think your audience will grow when you touch on nuance subjects such as this. At least, it drew my attention.

Direct to the point! 👍

For indexes, Not just the where but also the selects. Good video

Great video, thank you for doing content that is educative and entertaining at the same time! And I just wanted to add that the downside of creating an index is not that it takes up more space, the space overhead is minor, but the actual downside is that writes will take more time since it has to update multiple indexes instead of just one.

Good info!

We need more performance optimization videos!!

planetscale also has built in caching (currently in beta I think) think having the database provider handle caching is a great step considering they can simplify the process down considerably

Great video.

Excellent

Really helpful

The second thing that Cody mentioned regarding counting the number of rows in a table, I am not sure what type of database he is using MySQL or Postgres.But there is schema in the databases by the name of information_schema and that stores the information regarding the whole database, like when you execute SHOW DATABASES or SHOW TABLES; It fetch that information from information_schema. And one of the things that schema stores is information about tables and you can find out the number of records from the information_schema.TABLE table instead of using the COUNT(*) function. That number might not be the latest but can save you lot of execution time. Hope it helps. ☺

If you download the Prisma plugin, I think it suggests in your schema that you are missing an index on the model. Specially if you have relations. Nice video by the way!

You have found the secret sauce to high view videos. Showing people stuff like this A) to the point B) well explained C) not the usual everyday content

sir need more performance content

Hey Cody, cool video. Could you talk about in what scenarios someone should reach for prisma over something like a supabase postgresql database? Love your videos!

I wish supabase had same reporting UI as supabase damn that looks really nice

hi, I agree, prisma is great and table scans are to be avoided. much love

Useful video as usual! Could you also cover how to fetch data from database through REST api for admin dashboard that basically contains some graphs and statistics? For context, i have admin dashboard for my FYP project which is built using the MERN stack. The problem is, I dont think i did it properly, especially on the api endpoints and the way i fetch the data from the database using MongoDB aggregation

Whilst next does offer a way to do caching I think it's important to keep in mind that its building upon basic http or perhaps external caching principles which are applicable to any server application

PlanetScale insights is fantastic just recently did the same after realizing I missed 1 important index leading to hundreds of thousands of routed read in every request 😅 note down to 1 row read as well 🎉

Proper schema design (with indexes) and dropping Prisma for Drizzle made a big difference for me. For me since I had a bunch relationship based queries, which Prisma does a horrible job at, is probably what made the difference.

What about adding an in memory cache like redis

Is @@index can be applied to any data type?

Hate to be that guy, but which theme are you using? Been looking for a new one and I like this one

ID by default is index column so you could also look by index if logic in your code allow for this

This might be a stupid question since I lack fundamentals on SQL. But what specifically does an index even do the under the hood that it's able to optimize this? It's pretty cool that literally the solution is just @@index(column) and it magically fixes it.

doesn't getStaticProps run at build time? How does it know to revalidate data if it's already deployed? Or does it get built every hour?

Hey Cody I've been thinking of getting back to doing frontend and was wondering what you would recommend in terms of libraries etc for building like a facebook or twitter clone in 2023.

indexes will also slightly reduce write times so that's another downside, but still worth it usually to increase read times by not reading as many rows

The main reason is because prisma reads more than it needs, pure SQL would be to at better and put indexes on your data based on how you query

Do you prefer PlanetScale or Supabase for production app?

I'm wondering how would one perform the user and icon count caching in Reactjs? Maybe storing the counts in database?

Cody, I’ve wondered if there is much difference moving from using ORM to just SQL. what are the pros vs cons in terms of speed of requests and DX?

When a column is being referenced using a where clauses in SQL queries there are very few reasons not to have an index on that column, so I think your recommendation that you should wait until your database is in production and doing full table scans to add indexes is generally bad advice.

Since (I'm assuming) the email field is unique, I think it would improve performance even more by using a @@unique(email) index, or at that point just using an @unique attribute directly on the email field. If the identifier is strictly internal, using an `Int` with autoincrement would be even better (obviously don't use it if it's revealed to the user as it would give them an estimate of your user count at the very least, and open you up to scraping if you can view user profiles) Also, instead of using String and @default(cuid()), you'd save a lot of space with a `String @db.Uuid` and using a @default(uuid()) instead if you don't care between uuid/cuid. A uuid is just 128 bits to store, versus storing a cuid in a stringified form which 32 bits per character (at least 768 bits according to the spec's minimum length).

You have another query that has potential for problems. SELECT count(*) from (subquery that returns rows) The DB Engine is likely optimizing this for you now, but if its get more complicated and it actually has to return rows before counting you will face problems with high row counts. I just don't see why such a simple count would need be done with a subquery to begin with.

It is easier to delete indexes than add new ones especially if the table is very large. To make a new index the index tree has to be constructed based on the table, the bigger the table the slower this step takes. Removing an index is just getting rid of the tree much less work. So I would say it is better to be generous when selecting indexes on a table to be safe.

I have a feeling you'll get the same performance boost. If you marked the field as unique in your schema.

Here's an idea regarding showing user and icon count in your homepage. You can use redis to maintain the counts and every time a users gets added you just do an INCR to that key in redis. This will be way faster the counting all the rows in the DB and also would reduce a DB call.

You should definitely not index everything that often is queried unless it is a small app. Bigger applications indexing all where clauses will dramatically increase diskspace, and if you do not partition that you can end up in nasty situations

I didn’t understand why you dislike app router in this case. You could set it up to revalidate only the counter component instead of the entire page.

Switch from prisma to drizzle and it comes down an insane amount

planet scale requires a cc. any alternatives?

Yo what theme is that

you forgot that the heap needs to be rebuilt when new emails are inserted

Good clip for beginners. This is why using ORMs without prior sql knowledge is harmful.

I wouldn't even revalidate every hour maybe once a day is enough

isn't another viable solution to just store the count in another table and update it at every new user/icon and just read that one number each time instead of count * ?

The first thing you need to do just learn sql before try doing any backend stuff

Why having client side caching is better then having the same thing dictated by the server using simple http caching mechanizm? No need to update the client if caching policy changes and 0 code impact.

I don't know your app so don't mind my ignorance, but it's not clear to me why you even need that Trial table at all. The purpose of a primary key column is to uniquely identify a row - there's no need have a column named "id" in every table just for the sake of it. If it is the case that you always query this table by the email, then you don't need an id column because the email column is what already uniquely identifies the row and that should be the primary key. Or, if you're looking up the trial id to then search a TrialId column in a different table, why not just have the email column on the other table and drop the Trial table altogether? The id column is a string anyway, so it's not like its using a smaller data type for performance benefit or something. That way you can search the other table directly and still add your index on the email column, then you can retrieve the data in 1 query instead of having to do 2 separate queries. I understand that multiple trials could share the same email, but then I again still don't get the point of the table since you could just have the email column in the other place the trial is used. Or if you just need a table to store a list of all the email addresses in your system, that's ok -- for example a list of registered users, but seems like no since you showed the 2nd table with a userid, so I guess you also have a users table -- but if yes then just make the email the only column and the primary key - then you also don't need the index, and as referred to by someone else in the comments, inserting to this table then requires only 1 write instead of 2. Anyway, for the other table which had more columns where you added the index on userid, then yeah that one makes perfect sense and you should do that.

Learn sql Use explain keyword Play with sql And then say i am working as senior full stack

You need to optimize your skills with sql not your query man it is a b c sql databases use relations and joins