Select count(*) taking long time on MySql table
mysql, sql
Solution
It's probably an InnoDB table. Since InnoDB supports transactions, the table is never in a static state, parts of it can always be changing. The count() has to walk through and count every record, which is why it takes so long. Even then, it's more of an estimate, depending on the activity on the table.
A quicker way to get a close count on InnoDB tables is to look at the cardinality of a unique index (i.e. primary key on auto increment field). You can see this by running a "SHOW INDEX FROM table_name" command. The cardinality is the unique number of values in that index. For unique indexes, that's the number of records.
Problem
we have table in MySql DB with size approximately 35 giga bytes I ran a simple query ``` select count(*) from table_name ``` This query taking more than 10 min then connection getting disconnected, why it's taking so long time We don't have an primary key in our table schema, is this the reason?? If you need any other details I can provide here Thanks