Lets start with what I did...
I copied a table via PHPmyadmin operations/ copy table/(temp1) structure and data. nothing should make any changes to this copy.phpmyadmin shows 1185199 entries
I copy this structure to a new table(temp2). edit structure changing primary to int auto, and CHAR column to CHAR(16).
I then insert into temp2 select from temp1
Start strange part...

phpmyadmin shows 1187015 entries for temp2. looking at data with phpmyadmin tables match up to pg.47408 temp2 continues to pg.47481 I search ID > 1239186(last id on pg.47408) this gives 7056 results not the 1816 I expected.

questions: Why is the rest of the data not reported? why are my id's not matching number of entries? nothing has been deleted from main tables.

 Windows 10 pro on 1TB SSD
 MySQL version 8.0.27
 Apache server (handles PHP for intermediate between other machines)
  hardware: i7 4core(8 thread) 24GB Ram 

isolated network.

databases/tables: Innodb for all tables.
   5 main databases
      1 with 4096 tables on 2TB SSD (slowest growing, read the most of mains)
      4 with 1024 tables each on 4 or 8TB HDD(needed least often but most writes)
table structures are the same for all main databases
    4 Bigint columns, 1 CHAR(32), 1 Bigint Autoinc.

   1 work database on 1TB SSD 
     5 tables that get data from files via load
       4 Bigint columns, 1 CHAR(32), 1 CHAR(4), 1 Bigint Autoinc.
     2 tables for synchronization
        1 int autoinc., 7 int 

general process now:
  other machines "get work" via PHP script, sync. table is locked read updated unlocked.
machine finishes work- transfers files via network, calls php page which updates sync table again.

 server runs 5 tabs in web browser looping thru pages 1 tab per main database each 
      pg.1  get files to load lock and update sync table
             truncate work table
             load data
     pg.2 loops thru tables using insert select b1,b2,b3,b4,Char(32),null where sort = xxxx
     go back to pg.1

  • Temp tables have now been there for over 48 hrs and the results are still the same. Would restarting MySQL fix this?
    – tek-dragon
    Nov 6 at 19:33

1 Answer 1


InnoDB tables only show an approximate number of rows.

If, on the other hand, you executed SELECT COUNT(*) FROM table;, say so.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.