There is an issue with the result of counting the number of rows in the table

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 统计表行数,结果有问题。

| username: TIDB-Learner

The actual number of rows in a table is 1767, but select count(1) returns 3071.

| username: DBAER | Original post link

Take a look at the execution plan.

| username: tidb菜鸟一只 | Original post link

ADMIN CHECK table table_name to see

| username: TIDB-Learner | Original post link

The image you provided is not visible. Please provide the text you need translated.

| username: zhaokede | Original post link

Check the replication status of TiDB to ensure that there is no delay in data synchronization between the master and slave nodes.

| username: TIDB-Learner | Original post link

ADMIN CHECK table tb_xxx

8223 - data inconsistency in table: tb_xxx, index: PRIMARY, handle: 2356, index-values:“handle: 2356, values: [KindString 81EFAD65-FC94-493F-AD3E-1957DD2D4CE6 KindInt64 2356]” != record-values:“”
Time: 0.09s

| username: TIDB-Learner | Original post link

Non_unique with only one primary key | 0 index

| username: TiDBer_小阿飞 | Original post link

Check the execution plan, is it using an index?
See if there’s an issue with the index.

| username: caiyfc | Original post link

This has already reported a consistency issue. According to the phenomenon, there are 3071 rows of data when using the index, and 1767 rows of data when scanning the entire table, which also proves that there is an issue with the index.

| username: porpoiselxj | Original post link

This is probably due to an inconsistency between the index and the data. Try rebuilding the index.

| username: zhanggame1 | Original post link

How about count(*)? Is it the same as count(1)?

| username: TIDB-Learner | Original post link

Rebuilt the primary key index, and it works now.

| username: redgame | Original post link

It’s also the issue after importing the table.

| username: 小龙虾爱大龙虾 | Original post link

So, for the physical import mode of Lightning, make sure to pay attention to the error messages. :joy_cat:

| username: Kongdom | Original post link

I have also encountered this once. In what scenarios does this problem generally occur? Is it mainly during data import?

| username: TiDBer_rvITcue9 | Original post link

Rebuild the index.

| username: vincentLi | Original post link

Got it. It seems quite serious. count(1) is different from the number of rows…
In the past, when using Oracle, it was often said that count(1) is more time-efficient than count(*).
You really need to be cautious with lightning’s backend=‘local’. It’s similar to sqlldr’s direct=true in Oracle, which can damage indexes and requires rebuilding them afterward. Without rebuilding, I feel Oracle handles it better by directly marking the index as invalid. Perhaps TiDB could learn from this and avoid returning an erroneous result, as this is quite undesirable.

| username: Kongdom | Original post link

Hahaha, indeed, but in the end, it seems that they are almost the same. I guess the database backend has optimized it.

| username: shuyu_zhihui | Original post link

Thank you for letting me know that such a situation exists.

| username: zhanggame1 | Original post link

Oracle doesn’t have this concept either, or maybe it did 20 years ago. At least since I started using Oracle 10g, there hasn’t been such a concept.