Sign up ×
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 query which has 200 million Records in a single table. I am using BTree indexes in my table:

mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3;  
|    8242063  
1 row in set (3 min 23.53 sec)

I am not satisfy with this timing.

How can I reduce the result time less than 30sec. Kindly give me any suggessions! It will be more helpful to me!

share|improve this question

migrated from Mar 31 '13 at 1:04

This question came from our site for professional and enthusiast programmers.

You could use the explain keyword in front of your statement, you'll get a bit of information of what the query executes – Vengarioth Mar 11 '13 at 6:09
is the id unique across the table? Why do you do 'COUNT(DISTINCT(id))' instead of 'COUNT(*)'? – newtover Mar 11 '13 at 6:52
I also tried Explain keyword.. which give only table rows count.. – Zameer Ahmed Mar 11 '13 at 6:56
@newtover no, id is not unique in my table.. bcz of tat I m using distinct id – Zameer Ahmed Mar 11 '13 at 7:15
Can you add the output of SHOW CREATE TABLE tablename; ? – ypercube Jun 29 '13 at 8:53

4 Answers 4

Assuming you have an index on 'columnname' from the condition, and you really need 'COUNT(DISTINCT(id))', instead of just 'COUNT(*)' (that is the id is not UNIQUE or can be NULL), there is hardly anything you can do. What you request is basically an index scan starting from a given value '3', which is rather hard to express in other SQL terms.

share|improve this answer

If you are using InnoDB engine you can try partitions over you column (list and range preferable to test).

Another option:

  • add column is_more_than_2
  • is_more_than_2 = if(col > 2, 1, 0)
  • create index over this field and use it

But it will require some workaround to push this changes to production. Options:

  • long downtime
  • you can try do it without downtime but it will require a lot of workaround with additional table, triggers and replacing table at the end;

Hopes it helps

PS. Pure indexes will not helps you. Also you can think about re-implementing logic.

share|improve this answer

Maybe GROUP BY can help. See this MySQL reference on DISTINCT optimization

  FROM [tablename]
  WHERE [columnname] >= 3
) q
share|improve this answer
The following query gives me individual counts of each id.. I need whole table distince id count! thank you! – Zameer Ahmed Mar 11 '13 at 6:48
Edited answer and wrapped in outer SELECT. – Aiias Mar 11 '13 at 13:40
The given query was very helpful to me.. using this code I am getting the result within 1min.7sec.. thanks you! Do u have any other idea to optimize further to get a result within 30sec.. thank in advance! – Zameer Ahmed Mar 12 '13 at 9:40

Can you try the following?

select COUNT(id) from [tablename] where [columname] >=3 GROUP BY id;
share|improve this answer
What do you expect from this? – dezso Aug 18 at 10:53

Your Answer


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