MySQL fetch data problem.

1. I now have a table, no primary key, only one union unique (Date,user_id,count_name,)
   Table Record Example: date:20160808 user_id:556 count_name:a1

2. Date represents the day, user_id represents the user's ID, and count_name represents a statistic value.

3. Count_name desirable value (a1,a2,a3,,a12,b1,b2,b3,,b12);

4. There are tens of thousands of records generated per day.

5. The database has an upper limit of 10,000 entries per fetch.

I now need to count 12 values. You can say this with the MySQL statement:

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'); ···

This takes 12 times the database, can achieve the result, but the efficiency is not high.

Is there a way to take out the data you want at once? (This is a relatively old database, the amount of data is large, modify the database what to do the final consideration ha!) )

Tried method:

1. Paging statistics, because the table does not have a primary key, paging is not good processing.

2. Disposable data is processed, subject to MySQL can only remove 10,000 or so data at a time, and then more likely to crash.

Reply content:

1. I now have a table, no primary key, only one union unique (Date,user_id,count_name,)
   Table Record Example: date:20160808 user_id:556 count_name:a1

2. Date represents the day, user_id represents the user's ID, and count_name represents a statistic value.

3. Count_name desirable value (a1,a2,a3,,a12,b1,b2,b3,,b12);

4. There are tens of thousands of records generated per day.

5. The database has an upper limit of 10,000 entries per fetch.

I now need to count 12 values. You can say this with the MySQL statement:

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'); ···

This takes 12 times the database, can achieve the result, but the efficiency is not high.

Is there a way to take out the data you want at once? (This is a relatively old database, the amount of data is large, modify the database what to do the final consideration ha!) )

Tried method:

1. Paging statistics, because the table does not have a primary key, paging is not good processing.

2. Disposable data is processed, subject to MySQL can only remove 10,000 or so data at a time, and then more likely to crash.

