Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 1 table with 2.5 billion records(2.5 Cr.) and I am trying to get count of records based on indexed column. it takes 115 seconds to execute same.

Query : Select count(1) from table1 where column=code

Table1 Info

  • 2 billion (2 Cr.)
  • code gets resultset from DB with 1 to 10k rows,
  • Column is indexed.

Server Details

  • Intel Xeon 3.10ghz
  • 8 GB RAM
  • MySQL server 5.1
  • MySQL Connector 5.0.6

my.cnf details

  • Default Engine =InnoDB
  • innodb_buffer_pool_size=4G

Also take too much time for Update query also please suggest how do i tune mysql.

share|improve this question
    
2.5 crore is 25 million, not 2.5 billion. –  ypercube Apr 1 '13 at 18:32
    
Please run SELECT COUNT(1) FROM (SELECT COUNT(1) codecount,column FROM table GROUP BY column HAVING COUNT(1) >= 125000) A and post the query result back in the question. –  RolandoMySQLDBA Apr 1 '13 at 18:47
    
thank you..I have executed query but it has returned 0 count in 33 sec. –  ashish Apr 3 '13 at 5:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.