Mysql data retrieval problem.

1. I now have a table with no primary key and only one unique union (date,user_id,count_name, )
   Table record example: date:20160808 user_id:556 count_name: a1

2. date represents the date, user_id represents the user's id, and count_name represents a certain statistical value.

3. count_name possible values ​​(a1,a2,a3,···,a12,b1,b2,b3,···,b12);

4. Tens of thousands of records are generated every day.

5. The maximum number of records retrieved from the database at a time is 10,000.

I now need to count 12 values. It can be expressed like this using mysql statement:

<code>select count(distinct(`shop_id`)) from count_table where date = '20160808' and count_name in ('a1','b1'); 
select count(distinct(`shop_id`)) from count_table where date = '20160808' and count_name in ('a2','b2'); 
···</code>

Fetching the database 12 times in this way can achieve the result, but the efficiency is not high.

Is there a way to retrieve the desired data at once? (This is an older database with a relatively large amount of data. Modification of the database is a final consideration!)

Tried methods:

1. For paging statistics, since the table does not have a primary key, paging is difficult to handle.

2. The data is taken out for processing at one time. Due to the limitation of MySQL, it can only take out about 10,000 pieces of data at a time. Any more may cause it to collapse.

Reply content:

1. I now have a table with no primary key and only one unique union (date,user_id,count_name, )
   Table record example: date:20160808 user_id:556 count_name: a1

2. date represents the date, user_id represents the user's id, and count_name represents a certain statistical value.

3. count_name possible values ​​(a1,a2,a3,···,a12,b1,b2,b3,···,b12);

4. Tens of thousands of records are generated every day.

5. The maximum number of records retrieved from the database at a time is 10,000.

I now need to count 12 values. It can be expressed like this using mysql statement:

<code>select count(distinct(`shop_id`)) from count_table where date = '20160808' and count_name in ('a1','b1'); 
select count(distinct(`shop_id`)) from count_table where date = '20160808' and count_name in ('a2','b2'); 
···</code>

Fetching the database 12 times in this way can achieve the result, but the efficiency is not high.

Is there any way to get the data you want at once? (This is an older database with a relatively large amount of data. Modification of the database is a final consideration!)

Tried methods:

1. For paging statistics, since the table does not have a primary key, paging is difficult to handle.

2. The data is taken out for processing at one time. Due to the limitation of MySQL, it can only take out about 10,000 pieces of data at a time. Any more may cause it to collapse.