simple performance question

MySQL & MariaDB Other MySQL® Questions
1

I have a table with ~1.4 billion records. I want to check how many records are exactly there.
I have a Percona 5.5 server running on Ubuntu 12.04. The server has 80 GB of memory.
The table itself is ~100 GB.
I issue a simple command

Select count(*) from table

I then, monitor the INNODB engine. I get a reading of 35-45,000 reads/second
If my calculations are correct, it will need 9-18 hours just to do this simple command.
I thought that using databases would improve the speed of handling vast amounts of data. Is this realistic? Can I make my server faster?
I used the Percona my.cnf wizard to set all my variables.

Any help would appreciated.

Csaba

2

I am trying to execute this statement

select Distance, count(Distance) from hamming_q22_sd6_11 group by Distance

My table has approximately 300 million rows.
When I look at the process list it says

copying to tmp table

and it stays like that for hours. Something is not right. These things should take seconds not hours. I am really desperate, what am I doing wrong?

Csaba

3

Can you post the output of:

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;

please describe the hardware you are using.

Can you also post the EXPLAIN output for the affected queries as well as the schema for involved tables.