What is the difference between Count(*) and Count(1)? Which is more effective?

You are currently viewing What is the difference between Count(*) and Count(1)? Which is more effective?

What is the difference between Count(*) and Count(1)? Which is more effective?

Hi everybody.

When we count records in a data table, we are used to using count function to count, but there are many types of parameters that can be passed in count function, such as count(1), count() , count(column), …

So which one is the most effective? Wouldn’t count(*) be the least efficient?

People often consider count(*) to be the least efficient and assume that it will read all the fields in the table, like a query SELECT * FROM. Is this really true? Let’s find out below.

1. Which count statement will have the best performance?

We will start with the previous conclusion:

coun(*) = count(1) > count(primary key column) > count(column)

To understand this, we need to go deeper into the working principle of the count() function. Before going any further, let’s agree on the context of the article:

  • Database: MySQL
  • Store Engine: InnoDB

1.1. What is count()?

Count(arg) is an aggregate function, the parameter of count() function is a column, a constant or even (*). The function is used to count the number of records that meet the query condition and the parameter in the count(arg) function has a different valuenull . Suppose the count function argument is a field like this:

select count(name) from member;

This statement is to count the number of records in the member table with a name field other than null. In other words, if the value of the name field in the record is null, it will not be counted. Suppose that the parameter of the count() function is a constant 1, as follows:

select count(1) from member;

Of course 1 is always different null, so the above statement counts how many records there are in the member table. To better understand, let’s learn about the operating mechanisms and execution strategies of the count function.

1.2. How does Count(primary key) work?

When we count how many records there are through the count(arg) function, MySQL now maintains a variable called count and reads the records in one pass. If the arg value in the count function is different from null, it will add 1 to the count variable until all records are browsed and then exit the loop. Finally, return the value of the count variable to the client.

As we know, there will be two types of index: clustered index and secondary index. The difference between them is that the leaf nodes of a clustered index store the actual data, while the leaf nodes of a secondary index only store the primary key value instead of the record’s data . By default, clustered index will be automatically created when we create the primary key and InnoDB will store records in the leaf nodes of the B+ Tree.

Take the following command as an example:

select count(CardNo) from member;

If the table only has clustered index, no second index, InnoDB will browse on clustered index. For each record, InnoDB reads the primary key value to compare withnull . If it is different null, the count variable will be added by 1. 1.jpeg

Another example Phone_Nois the primary key and there exists a second index (Phone_No, Name)in the table Member.

2.jpeg

However, if there is a secondary index in the table, InnoDB will not browse the clustered index, but the secondary index. The reason is that secondary index can take up less storage space than clustered index => secondary index tree is smaller than clustered index tree => I/O cost when scanning second index is lower than scanning clustered index. Therefore, the optimizer prefers to use secondary index.

1.3. How does Count(1) work?

The parameter of the count function is 1, which is clearly not a column, nor is it null. So count(1)how will it work?

For example:

select count(1) from member;
3.jpeg

In the case of only clustered index and no secondary index, InnoDB browses the clustered index to count records, but it only browses records, without reading the value of the primary key . Because the existence of a record is considered different null.

We can see, Count(1)it will be faster Count(primary key column), because it does not need to read the record value to compare with null. However, if there is a secondary index in the table, InnoDB will browse the secondary index first. 4.jpeg

1.4. How does Count(*) work?

When you see the * character, do you think it’s reading all the field values ​​in the record?

It will be true in the case of select *, otherwise count(*)it won’t. When we call count(*), MySQL will convert the parameter to count(0).

5.jpeg

Therefore, the implementation of count(*) is identical to count(1) and there is no performance difference.

1.5. How does count(column) work?

We have the following example:

select count(name) from member;

For this query, MySQL scans the entire table to count, so Count(column) has the worst performance compared to count(1), count(*) and count(primary key column).

6.jpeg

In case the column has a secondary index, the command will use the index to browse, thereby improving speed.

1.6. Summary

coun(*) = count(1) > count(primary key column) > count(column)

Prefer to use coun(*)or count(1).

If there is a secondary index in the table, InnoDB will choose the secondary index to browse. Because it is more efficient than browsing the primary index.

If there is no second index above column X, it should not be used Count(column X)to count the number of records, because it can scan the entire table => least effective.

2. Count in other cases

2.1. How is Storage Engine different from count(*)?

You may be wondering why the count() function needs to iterate over records?

From the beginning of the article, I only mentioned the InnoDB storage engine, but different storage engines may have different ways of implementing the count function. For example, MyISAM, another MySQL storage engine, is the second most popular after InnoDB.

In the case of using MyISAM, each table will have metadata containing the value row_count. So when needing to count all records in the table (count() without filtering conditions), MyISAM only needs to read the value row_countwith O(1) complexity .

When count() has a filter condition, MyISAM and InnoDB behave no differently. Both need to scan the table to count the number of matching records.

*Note, when reading row_count, MyISAM locks the table to ensure consistency of this value.

2.2. How does Count in transactions work?

Storage engine InnoDB supports transactions, multiple transactions can be executed at the same time. MVCC (multi-version concurrency control) and Isolation mechanisms can affect count() results.

For example, the member table has 100 records. And there are 2 sessions executed in parallel and the queries are executed in the following order:

isolation-count.drawio.png

At the end of session A and B, we check the total number of records in the member table at the same time but you can see that the displayed results are different. Because the default isolation level of transaction A is repeatable, count(*)the second will repeat the result as 100.

InnoDB needs to browse data in undo logs to ensure transaction isolation. You can read more about MVCC and isolation calculation.

3. How to optimize count(*)?

If you often use count(*) for a large table, it’s not a good solution.

For example, the member table has a total of 12+ million records, and I also created a secondary index, but it takes about 5 seconds to execute once:select count(*) from member

8.jpeg

So is there a better way to do this when facing a large table?

3.1. Get approximate value

If you don’t need to be very precise about the statistical number, for example when a search engine searches for a keyword, the number of search results given is an approximate value.

9.jpeg

In this case, we can use the explain command to estimate the table. The EXPLAIN command (without the ANALYZE parameter) is very effective because it does not actually query.

10.jpeg

3.2. Create a table to store the count variable value

If we want to get the exact total number of records in a table, we can store this count value in a separate count table. When adding a record to the data table, we increase the count field by 1, and when deleting a record, we decrease the count field by 1.

summary

Finally, we need to remember a few important points:

  • Count(*) has better performance than Count(pk), count(column)
  • The count function prefers to use secondary index to perform counting.
  • If exact figures are not needed, take approximate values.

See you again in the next articles.

If everyone finds it interesting, please give me an upvote 🔼 and share.
Thank you everyone so much 🙏

Source : https://viblo.asia/p/su-khac-biet-giua-count-va-count1-cai-nao-hieu-qua-hon-vlZL9a2BLQK

Please follow and like us:
fb-share-icon
Tweet
Pin Share

This Post Has 167 Comments

  15. What is Considered a high roller At a casino July 10, 2025 Reply

    You can also hear them known as wagering necessities or the bonus
    rollover. They specify the variety of actual cash wagers you must place earlier than withdrawing money received out of your
    VIP bonus play. AdamEve Casino – Licensed in the Netherlands Antilles, this on-line on line
    casino doesn’t settle for American gamers.
    They do provide a pleasant bonus for high roller players, nonetheless, and a 600% windfall can be had up to $3000.

    Excessive curler on line casino bonuses come in a number of varieties,
    however all of them permit players to acquire large amounts
    of free money from the gaming web site. The finest high roller on-line casinos offer you precedence treatment, high table limits,
    and customized bonuses. Whether Or Not you’re betting massive on blackjack, video
    poker, or slots, these casinos cater to critical players who demand extra.

    By providing high-roller casino bonuses, a casino shows interns
    which they’re ready to speak about the hazard with players.
    Since they got a lot of experience in enjoying on line casino video games, high-roller bonuses are frequently redeemed by skilled gamers.
    Deposit bonuses for prime rollers are tailored to suit the
    size of your deposits.
    Wonga Video Games on line casino welcome bonus suggests a chance to receive a
    1000% match-up bonus, escalating to £2000. This promotion is
    out there for the first depositors who partake in the Bonus Wheel.
    The Free Spins, valued at £0.10 each with a most win of £100, are credited after the
    qualifying deposit is wagered 1x and have
    to be used inside 7 days. Winnings from spins are also subject
    to 40x wagering throughout the similar interval. Slots and parlor
    video games contribute 100% towards the requirement, making them the
    most effective way to full wagering.
    Large 500% bonus as a lot as $7,500 and 150 free spins to welcome new players.
    Elevate your gaming experience with elite rewards and
    VIP perks only at HighRoller On Line Casino. Discover these subjects to boost your
    understanding of High Curler Casino Bonuses and elevate
    your on-line on line casino experience. To activate this bonus, you have
    to make a qualifying deposit of a minimum of C$20.

    The bonus is relative to your deposit, and the more you deposit, the extra bonus you get.
    Bojoko’s top Canadian on-line casino critiques are there that can assist you see What is Considered a high roller At a casino the on line casino is basically
    like. In addition to the bonus cash, you will also get 200 bonus
    spins along with your first deposit. There isn’t any assure that the wins will be
    larger on high restrict slots, but they generally
    have higher payout percentages and better
    profitable odds, making it more possible to get larger wins.
    This is a slot with a enjoyable 80s theme, however its options are trendy and
    appealing to those of you who need to place higher bets for greater wins.
    High limit slots typically characteristic more favorable return rates
    than normal slots. Taking Half In with more cash signifies that fixed-money payouts, that are
    the overwhelming majority of slot payouts, are higher.
    Typically additionally known as “casino whales”, high rollers are gamers who wager a lot
    of money in on-line casinos and stand out from the crowd.
    Because of their huge wagering habits, high rollers typically
    turn out to be VIP players, earning rewards from the casinos and being treated in one other way.
    Bonuses for brand new and present players are a means for on-line casinos
    to encourage the folks to register and try their offer
    of video games. There are at present sixteen bonuses from Shangri La Casino in our database,
    and all offers are listed in the ‘Bonuses’ part.

    For those who prefer classic table games, they have many versions of blackjack,
    roulette, and baccarat. And if you’re into
    the reside on line casino vibe, there are over 200 reside vendor tables powered by
    top providers like Evolution Gaming.
    Excessive roller casinos aren’t only created for prime rollers, contrary
    to popular belief. In fact, many excessive rollers casinos are literally made for every player
    to get pleasure from. If you are planning to make use of Skrill and
    Neteller for deposits, it is important to know that these
    two deposit methods do not appeal to a bonus.
    Every spin on the 7’s Slot Machine prices $5,000, so it’s a spot
    for premium players and whales. On the positive facet,
    the Spin Excessive Limit Room has its personal butlers and an exclusive dining area.
    Mandalay Bay and Wynn/Encore Las Vegas even have glorious high curler rooms.
    The Venetian’s high roller slot parlors also feature the ability to request your favorite branded slots.
    A high restrict cashier cage assists the guests with questions and payouts.
    The LED televisions are in every direction, so you presumably can watch your favourite show or sporting
    event whereas enjoying the slots. Subsequently, use the few
    slot suggestions under as your technique to realize and preserve the
    biggest winnings.

Leave a Reply