I had a strong feeling that something like this is going on in DB engine, but only "argument" I had is pretty weak: "It would be very stupid if DB engine pulled all data from all columns just to pass it to count() function and return single int", and it would be very easy to implement such optimization. So I was using select(*) while simply trusting the DB to do the right thing. Thanks to this great video, now I have a confirmation and also know exactly how DB decises how to count rows in optimal way. Great video :)

The most important take from the video is not about count(), but generically that (if speed is critical) you should always review the execution plan. Easier to do while at the prototyping phase, but can still be accomplished in production, just needs more testing and QA.

What about counting in OurSQL?

This is actually incredibly educational. Thanks, planetscale!

"You'll have won the argument, which is what the holidays are all about." 🤣

"todos" also means "everything" in Spanish. So, "Select * from todos" means "select everything from everything". Terribly inefficient but it'll be great fun writing the code to sort out what we want from the results 😜 - a grizzled vet.

With count(1) you get the performance boost without all the confusion

I might call myself a somewhat senior programmer. Sometimes query optimizers did not realize to use a clustered or whatever else indexes of a table when using count (*). This happened at least on Oracle 8 and the workaround was to use count ([indexed column]) where [indexed column] = something. Count (*) caused full table scan in some cases, at least if table contained lobs. So there might really be a reason why some grey beards warn on count(*). When in doubt, check the execution plan.

A couple of corrections: The COUNT() instruction can receive as parameter an EXPRESSION or a wildcard, that is to say, you could write: COUNT(*) or COUNT(1), COUNT(pepito), COUNT(id) or COUNT(99999) which will give you the same, the inside is considered a wildcard, the "*" is used as a wildcard by convention , but we could use any character because by definition, it doesn't use information about any particular column (and including for the COUNT the rows that contains NULL values in any column). In the case that you comment the COUNT(id) and the COUNT(*) bring the same result because the "id" is declared as if it was a wildcard so the behavior is the same and the server takes the license to optimize the process as you have explained in the video. But, if you really wanted to count the values of a field, the correct way would be to specify COUNT(ALL id) and this expression does have a difference with respect to the COUNT(id), and it is because it will only consider for the count the NON NULL values inside that field In the case of the example of the video COUNT(id) and COUNT(ALL id) should return the same result, since the "id" field, being a primary key, would never be empty, but the difference would be that you would force the server to use the index of the primary key to execute the COUNT(ALL id). Finally, while it is true that the server often saves us from ourselves, it is not exactly true that it always makes the best decisions, as a DBA with over 10 years of experience I have found myself in several situations where after checking the execution plan I realize that the server is taking a not so optimal index for the instruction that has been requested and you have to address it to use the correct index for some instruction, this is seen quite often in big data querys.

primary keys are clustered, aligned to disk clusters, physically, so counting them means traversing the disk to gather the count, and if the order on disk is not adjacent, then the index is fragmented so counting can take a lot of time, while non primary keys or indexes are no clustered, meaning they don't need to follow the disk physical alignment so they are most often stored off-table in a much more compact data structure, which even when it gets fragmented, the data is still going to be close to each other because all the data structure holds is index records, not every row in the table, like what each clustered index follows.

Thanks for this. I've had this argument so many times. I think some early SQL engines did look at all columns for count(*), but I believe pretty much all of them have this optimization at this point.

A nice gotcha indeed! One question : this "optimization" -- is it applicable ONLY to MySQL or is also the case with say, Postgresql ??

for specific purposes (e.g. extremely large table, statistics, etc) i set up a count-table with a single row and column, holding the information of the count of rows of the "parent table". this requires setting up triggers on the parent table insert+delete procedures to increment+decrement the value of the count-table. keep in mind that this setup slows down the process of writing data, but data is usually read many times in contrast to written.

Does this mean adding a non-null secondary index will improve count performance on tables that don't already have one?

Just subscribed. Hard to find people who actually talk real facts these days.

Yes, very often the problem you are trying to solve is more generic, and can be expressed in more generic terms. Using language features and trusting that smarter people have put more effort in optimizing the language itself is often more optimised than what we can come up with ourselves. One classic C trick was to not use multiply/divide and instead add or subtract bitshifted values. However, in modern systems that no longer take dozens of clock cycles to multiply, the compiler knows better and will just replace your whole expression with a multiply.

"You'll have won the argument which what holidays are all about" - best sentence I've ever heard. :D

which also means it needs a secondary non null index to function the way you indicated. redo the trial without such an index to see what it does

This is the type of content I didn't know I wanted. More please.

Two thumbs up to you. My TL was at times bugging me on some reviews. Even when I showed him SQL docs saying that most optimisation is already being done by the MySQL there's no need to over engineer the solution.

With a proper SQL implementation, this should not matter; the compiler should handle this.

I've been using COUNT(1) for a while now. I wonder if the behavior is the same in other databases such as CockroachDB

So why didn't they just make the Count function parameterless. Select Count() from customers

How is someone talking about SQL this charming

I should create a database engine where COUNT(*) is "multiply number of columns by the number of rows", COUNT(/) is "divide rows by columns", COUNT(+) is ... (you get the idea)

actually, WAY back in the day in Oracle (1990s), it was recommended to use SELECT COUNT(1) and not COUNT(*) because it actually did make a difference. but they fixed that. but some grizzled old devs kept that convention.

This was not always the case though. Count(*) was always slower. Not by a little.. by a lot. That saying i havent used my mysql in 10 years. But with my experience with postgres, it didnt matter

Always nice to see how the optimiser is working under the covers. I’ve seen a few cases where the original program had done something dumb but the optimiser picked up the issue an optimised the issue away. Still makes me uneasy relying on it though.

6:15 "Over Thanksgiving, when you're arguing with your family about whether count(*) or count(id) is faster" I'm sure grandma will love to talk about that ...

I stopped using count(*) decades ago when I ran into a problem with our database engine at the time where there was some catalog corruption and this was erroring out with "column not found error". Lately I've been using count(1). Likely not a great reason to continue not using it (and I'm expecting the execution plan to be the same in any case).

Great stuff! Love that you mix in a bit of fun with the content, it's what got me to subscribe!

Like the EXISTS function where the "SELECT * FROM ..." is just a predicate for the function to work and only the WHERE clause is meaningful

In a client project a few months back, we had to remove pagination from an admin dashboard, because COUNT(*) to get the no. of pages was timing out. even after hours of letting it run. The table we tried to scan was over 5TB in size. How would you count these kinds of tables in MySQL? (we couldn't easily migrate to something like Postgres for other reasons).

This is also true in MS-SQL. it will use the most narrow Non Clustered Index on a Table. If there is non, the Tables clustered Index has to be checked, which is slow. but i think in MS-SQL you can use sys.sysindexes to look up the rowcount even faster

Please make more education SQL content. This was fantastic.

The advice to avoid count(*) predates mysql. It may have even been true in mysql at some point or some obscure schema. As you said, count(*) relies on the optimizer to do the right thing. I use count(0) myself, but I wouldn't be surprised if sometimes you have to be more specific to get the right query plan. I think the biggest lesson in this video is not to rely on advice, but to check the plan and know that counting an index can be faster--which is great advice!

Love it! Thanks guys for sharing

if you remove secondary indexes, and run count again, how long it takes?

count star is asking recordcount and that is table property. When select * is used in columnar database then it is not optimal since in case columnar databases columns are retrieved separate and then records are put together. In case of columnar databases for null allowed columns also distinct count is also column property sometimes also min and max etc. Vertica has for example no indexes at all.

Granted it "might" be based on the sql engine: Oracle, Sql Server, Postgres, ect.... i usually do `select count(1) from MyTable;`. Whether that is faster or not i don't know & it might depend on the engine again.

on mysql

If you count a primary key, why wouldn't the SQL vendor recognize the intent and treat it the same as * anyway? Can count(id) where id is the primary key ever lead to a different result than count(*)? I wish he tested with multiple SQL vendors(MySQL, Oracle, MS SQL, PostgreSQL, SQLite), with * and with id to see if all or just some are optimized this way. I wish he also timed some of them in milliseconds too to see how important the difference is. Not all SQL vendors optimize the same ways so I'd be surprised if this advice is applicable to every SQL vendor that exists. If I submitted count(*) in a pull request and the reviewer didn't like it, he might not be convinced by this youtube video because it doesn't go into any depth on evidence with different SQL vendors or actually timing anything. It would be a lot more efficient in man hours if a video like this included enough evidence and detail than if the masses who tried to follow the advice did all this testing independently on their own.

This make sense when you select all rows from table, but throw in there any WHERE clause, or any filtering then the advantage might evaporate. The hardest thing i found was to display results on filtering ... In this for example might you want to show how many todos are done from total. This specific table does not have the field 'done' but if its having done, and was not specified in a key it will result in a table scan.

One of the best videos I haveever watched on SQL, tq

"Tell your family on thanksgiving" It would take me about 5 winters to explain this to my family.

Would be interesting if the same is true for other sql engines like litesql, postgres, sql server and postgres

Wow, using mysql for decades and never knew this. Thanks man.

“When you’re arguing with you family…” haha nice one.

Why don't DBs store a count variable and save the day? Why don't they count ROWS by the rows themselves` instead of ROWS BY COLUMNS?

I'm a vet and will use *. I don't care if it bothers anyone, because I want to just move on. However, I Love this video and the knowledge you share.

Doesn’t the compiler optimization demonstrated at 5:25 mean the recommended usage of count(*) makes no difference in practice?

- *Newbies:* Using COUNT(*) - *Dinosaurs:* Using COUNT(ID_COLUMN) - *Me and my two neurons:* Using COUNT(1)

Looking forward for the next argument with my family about the performance of the SQL count operation. Everyone will be so excited

0:55 >optimized This optimization is specifically MySQL case, in other DB it may not be so and can even depend on DB version. So generally for SQL count(id) is better (if there is suitable index of course).

I knew the db optimizes the query, but didn't know details like this. I would be surprised if after 20+ years of development, it would interpret count(*) as "load everything from the table and count it". What about when you don't have a secondary index, or doing a join query? Probably still counts the returned rows, or some optimization with joined indexes?

this tested by me select count(*) is fastest than count(id) .. but only few milliseconds different. The reason already explain here. if you have some complex query and need to count how many the data.. select bla-bla etc just make the query as sub query like this select count(*) c from ( select bla-bla ) tbl1 * found where my older script using select bla2 and count using mysqli_num_rows (auch)

Maaan, I didn't know this channel is posting such content :0 I liked it, subscribed and activated notifications.

I never got into using SQL databases, but having written a few on-disk and over-the-network data structures, I'd expect the count(*) to be smart enough to use some cached "total_length" value, especially considering that a lot of effort went into writing query optimizers. I guess, people would think that because of lack of experience of writing a data store yourself?

Best ad ever, keep them coming

Cools, I am using MySQL for years but never heard of this one. Please more videos like this :)

It's database engine dependent. Oracle, SQL Server, DB2, MySql and etc. all have different implementations. In SQL Server there are faster ways of getting a count then using select count(*) which incidentally puts locks on the table being counted if you are not careful.

That's the thing about Sql, Sql is not like a normal programming language,... It takes the query, analyse it, plans it, then runs it

It seems to me that the point of a declarative language is that you declare what you need, or tell the computer what you need. The computer figures out the best way to get it for you. Telling the computer how it should count seems to be defeating the purpose of having a declarative language where the computer will figure out the best way to do what you requested.

I know about count(*) but I didn't know how the Optimizer decides about what index to use. One learn new things everyday, Thanks Aaron!

7. Crea un rol que permita conectarse a la base de datos y hacer SELECT sobre las tablas creadas anteriormente. CREATE ROLE ROL_USU; GRANT CREATE SISSION TO ROL_USU; GRANT SELECT ON TABLA1 TO ROL_USU; GRANT SELECT ON TABLA2 TO ROL_USU; GRANT SELECT ON TABLA3 TO ROL_USU

8. Conceder el rol creado a USU2 y USU3. GRANT ROL_USU TO USU2, USU3 9. Conceder a USU4 privilegios sobre algunas tablas con la opción de poder concedérselos también a otros usuarios. GRANT INSERT, DELETE ON TABLA1 TO USU4 WITH GRANT OPTION; GRANT INSERT, DELETE ON TABLA2 TO USU4 WITH GRANT OPTION;

I felt like the video was not finished. I didn't see you run any thing. You just said it was faster, but I wanted to see the numbers.

thanks for the educational content

Very nice! Thank you!

I think this video is a bit misleading. The part about count(*) vs count(id) is good and explains well why they are equivalent, also the explanation about the index used by the database is informative. However, saying that count(*) is fast is not accurate - depending on circumstances it can be fast or it can be very slow. Usually, when tables grow into millions of rows SELECT count(*) becomes problematic. Sometimes mysql optimizer is not smart and will use a wrong index - for example, I have a table with 750 000 rows and for SELECT count(*) mysql chooses a secondary nullable index even though a primary index exists! I don't know how a nullable index can be used for count(*) but certainly not efficiently because the query takes 2 seconds, which is quite slow. A table without a primary index of 2 million rows can take as much as 2 minutes to count all rows! So overall, the statement that SELECT count(*) is fast is what I find misleading in this video. This method of counting should not be encouraged unconditionally, actually people should be aware of performance problems they may come across. So while a myth, not entirely a myth!

It's actually written in the manual. You don't have to prove it. It surprised me when I read it.

Holy count, I just found an awesome channel to subscribe to. Love the humor at the end!

maybe i am in the wrong circles but i've never worked with anyone who said don't use count(*) weird, did you just make this up? or do people actually say this?

I can't wait to go to the next thanksgiving.

You could also use the „rows“ from the explain query. In some use cases this is already good enough ^^

What a great video. Earned a subscribe. Looking forward to more!

I love your style!

Thank you for saving everyone's Thanksgiving! 😂

I verified the explain plan of count(*) on a table, optimiser even picked a secondary index on a null column.

When I was still coding DB scripts “select count(1) from table” was considered fastest.

Your thanksgiving conversations sound interesting

Me using count(1) like a caveman.

if count(id) and count(*) uses due-date in the end, then does it matter what you use?

I didn't know this. Thanks! Has it always been this way?

Ok thanks, I'll introduce this to my entire family, specially to my gran grands

Is 'select count' still always going to be a table-scan query? Or are there internal optimizations, that maintain a count of active rows in a table.. maybe an in-memory cache that's updated atomically with insert/delete operations?

This changes by database server. This is true for MySQL but SELECT count(*) is much slower in SQL Server. In SQL Server the way I learned to to do it was SELECT count(1)

I actually learned something new today! Thanks!

What software are you using to see which column is read? Is there an extension for this postgres?

All other dbs only return one field with count(*). Have never seen a table when running that query

Good video, thanks

thanks for this info

Now I'm ready for the holidays 💪🏽

very informative!

Thanksgiving comment totally, 100% EARNED the like. Cheers.

Thanks so much for providing this great information.

Hey dude, your way to explain this topic is very well !! Congrats

Wow, thank you!

you are amazing at explaining things man

Awesome, just 1 sidenote; If, like you said at the start of the video, you want to get the row count of a table, just ask the table instead of counting it... 😅