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 query like this! 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;
+------------------------------+
| COUNT(DISTINCT id) |
+------------------------------+
| 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!

thanking you!

share|improve this question
1  
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
show 1 more comment

migrated from stackoverflow.com Mar 31 '13 at 1:04

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

3 Answers

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
add comment

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
add comment

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

SELECT COUNT(*)
FROM (
  SELECT 1
  FROM [tablename]
  WHERE [columnname] >= 3
  GROUP BY id
) 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
add comment

Your Answer

 
discard

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