Tell me more ×
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.
up vote 0 down vote favorite

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 at 6:09
is the id unique across the table? Why do you do 'COUNT(DISTINCT(id))' instead of 'COUNT(*)'? – newtover Mar 11 at 6:52
I also tried Explain keyword.. which give only table rows count.. – Zameer Ahmed Mar 11 at 6:56
@newtover no, id is not unique in my table.. bcz of tat I m using distinct id – Zameer Ahmed Mar 11 at 7:15

migrated from stackoverflow.com Mar 31 at 1:04

3 Answers

up vote 0 down vote

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
up vote 0 down vote

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
up vote 0 down vote

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 at 6:48
Edited answer and wrapped in outer SELECT. – Aiias Mar 11 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 at 9:40

Your Answer

 
discard

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