CODEWITHSUNDEEP
mysql
init3
init3

Reputation: 43

MYSQL Count(Distinct) Group By

This select keeps timing out:

SELECT COUNT(DISTINCT `invoices`) 
FROM `data` 
WHERE date BETWEEN '2013-07-01' AND '2014-06-30' 
GROUP BY `store`

I have made indexes on invoices, date, store and invoices, store, date. Any help is appreciated.

Upvotes: 4

Views: 7085

Answers (3)

Marcx
Marcx

Reputation: 6826

try

SELECT `store`, COUNT(*) FROM 
   (SELECT `store`, `invoices`
          FROM `data` 
         WHERE date BETWEEN '2013-07-01' AND '2014-06-30' 
       GROUP BY `store`, `invoices`
   ) tbl
GROUP BY `store`

Here's a fiddle, without the date column and check...

http://sqlfiddle.com/#!9/fb83c/5/0

Upvotes: 6

ahmet
ahmet

Reputation: 61

I also had the same problem. I think you are working with a big table. I solved it by changing some values in configuration file: 

key_buffer = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M

innodb_file_per_table
innodb_buffer_pool_size = 4G 

innodb_log_file_size = 512M
skip_name_resolve

join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M 

key_buffer_size         = 64M
sort_buffer_size        = 1M
tmp_table_size          = 256M
max_heap_table_size     = 512M
read_buffer_size        = 64K
read_rnd_buffer_size    = 64K
myisam_sort_buffer_size = 256M

But these values are changing according to your pc and work. I suggest you to make some search about "mysql configuration for big data". You will find some information about their meanings. By the way you should also have a primary key in your table. I think it also affects the performance. Finally you should check the preferences in mysql. You may need to increase the mysql session settings under the sql editor section. I hope it helps.

Upvotes: 1

Rohit Gupta
Rohit Gupta

Reputation: 4191

Try this

SELECT store, COUNT(DISTINCT invoices) 
FROM data 
WHERE date BETWEEN '2013-07-01' AND '2014-06-30' 
GROUP BY store

Upvotes: 1

Related Questions