CODEWITHSUNDEEP
mysqlinnodb
user4448
user4448

Reputation: 21

innodb table count optimization

I've a big innodb table which contain 10.000.000 rows

this query SELECT count(id) FROM table_name takes 4-6 seconds to execute

I need to decrease the query execution time

1) can someone advice how to achieve this without changing the table to MyIsam

2) In case we need to use a MySQL cache how can we turn that ON on the server?

Upvotes: 1

Views: 454

Answers (2)

akuzminsky
akuzminsky

Reputation: 2257

Lie about the count in the application. Really, in rare cases you need exact number. Approximate row count (but as fast as select count(*) in MyISAM) you can get from 

SELECT MAX(id) - MIN(id) AS count FROM table

if you still need the exact number you can create a table with the count number and update it with a trigger ON INSERT and ON DELETE

Upvotes: 2

Aniket Bochare
Aniket Bochare

Reputation: 427

If your table does not change often, using the MySQL query cache is a good solution. 

[mysqld]
query_cache_type = 1
query_cache_size = 10M

Also add index on field 'id' if it doesnt exist. Also if you are writing an application store count in a seperate table as you insert or update. Its handy.

Upvotes: 0

Related Questions