3

Same database imported 3 three times after empty the entire database and surprisingly every time it shows different number of records. Why?

1st time import: enter image description here

2nd time import: enter image description here

3rd time import: enter image description here

It is not right to trust on Rows count as shown in picture it show approxmiate value as error suggested. So the question is how can we ensure that database is right and no record missing? note: short-cut require can't use count with each table it will lots of time.

Muhammad Faizan Khan
  • 10,013
  • 18
  • 97
  • 186

5 Answers5

11

MySQL is, surprisingly, really bad at numbers. For InnoDB tables those are often estimates of how many rows it contains and they can be wildly wrong.

The way it computes the numbers you're seeing is by taking the total size of the table data and dividing by the average row size in bytes. This is usually a good enough approximation of your data, but it can be very misleading, off by a factor of up to 100.

The only way to know for sure is to do COUNT(*), something that can take some time to compute on a very active table.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Thanks, I can ensure every table by executing this query is there any shortcut? – Muhammad Faizan Khan Apr 26 '16 at 05:48
  • There's no short-cut. On a server that's idle this will usually take milliseconds per table. On one with extremely heavy write activity it can take upwards of minutes, it really depends. Try it out and see how it performs for you. The numbers you're seeing there are quick and dirty, they rarely take long to produce. – tadman Apr 26 '16 at 05:49
  • i try it on everytable its time consuming – Muhammad Faizan Khan Apr 26 '16 at 05:50
  • It depends on your computer hardware and your InnoDB tuning parameters. If the InnoDB engine is starved for memory performance might not be that great. There's a [large section in the manual devoted to InnoDB parameters](http://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html) as well as many guides on the internet in general. These tables all have a `PRIMARY KEY`? – tadman Apr 26 '16 at 05:57
  • concludingly i have to execute query for every table in order to ensure it. thanks – Muhammad Faizan Khan Apr 26 '16 at 06:05
3

Tools like phpmyadmin/adminer always picks the row count from INFORMATION_SCHEMA. In case of InnoDb storage engine the row count is a rough estimate, it is never the exact value. The table_rows which phpmyadmin picks which is never accurate for Innodb

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'table_name';

For exact value we need 

SELECT count(*) FROM 'table_name';

For reference: http://dev.mysql.com/doc/refman/5.7/en/tables-table.html

mysqlrockstar
  • 2,536
  • 1
  • 19
  • 36
3

You'll notice that all of the "negative mysql records" This isn't a negative sign its ~, which means approximately. if you want actual count use 

SELECT count(*) FROM 'table_name';
abhayendra
  • 197
  • 6
2

I wouldn't rely on comparison of numbers with a tilde (~) as prefix.~ means approximation.

Based on bencoder response to this phpMyAdmin - What a tilde (~) means in rows column? the approximation can vary a lot.

To check the real number of rows imported use: select count(*) from TABLE_NAME

Community
  • 1
  • 1
Dario Corsetti
  • 76
  • 3
  • Thanks, I can ensure every table by executing this query is there any shortcut? – Muhammad Faizan Khan Apr 26 '16 at 05:49
  • A nice solution is reported from Nathan on this question http://stackoverflow.com/questions/286039/get-record-counts-for-all-tables-in-mysql-database. (Sorry but I cannot paste the code here because is not correctly parsed) – Dario Corsetti Apr 26 '16 at 06:05
1

The number you are seeing is approximation. To get the actual number JUST CLICK ON THE NUMBER. You will see the actual number. You do not need to run any query to view actual row number.

Luton Datta
  • 54
  • 4