CODEWITHSUNDEEP
mysql
Ami Malimovka
Ami Malimovka

Reputation: 457

select count(*) on a big table mysql

I have a big table (approx. 150 M rows), and if I try to run a simple select count(*) on it, then mysql works for about an hour and then throws an error.

I'm assuming this is not due to a limitation of mysql, rather a problem on my side, but I have no idea where to start looking. any ideas?

the table is innodb mysql 5.5 on linux

Upvotes: 2

Views: 3686

Answers (4)

Anubhav
Anubhav

Reputation: 147

You can use Information Schema.

SELECT TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = YOUR_DB_NAME AND table_name = YOUR_TABLE_NAME

Upvotes: 0

echo_Me
echo_Me

Reputation: 37243

The easiest way to speed up queries like this is with a covering index. This will allow you to scan through the rows you want, but require fewer bytes of I/O per row (since you're only scanning a portion of each row's data, not the entire row). Furthermore, if your index is sorted in the same way that your query is, you can avoid the cost of a sort and can scan vastly fewer rows.

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

You can use count(1) instead of count(*)

Try ::

Select count(1) from my_table

Upvotes: 1

silly
silly

Reputation: 7887

you can check it with table status like this

  SHOW TABLE STATUS FROM db_name LIKE 'table_name';

you see the rows column....

Upvotes: 2

Related Questions