Re: Slow queries with very large table

[Peter Brawley]

> SELECT count(pk) where TABLE;

I guess you mean ...

SELECT count(pk) from mytable;

1. The speed diff from 1st to 2nd call is due to caching.

Do you really expect 9,223,372,036,854,775,807 rows?

pk INT UNSIGNED will give you space for 4 billion rows, and it'll be faster.

2. While you're at it, drop the other indexes, Optimize the table, then recreate the indexes.

3. What's innodb_pool_buffer_size, RAM?