Mysql query optimization

Question

I use MySQL and have a user_data table as follows:

 user_id int(10) unsigned reg_date int(10) unsigned carrier char(1) 

reg_data is the timestamp of the registration time of the registration time (it can be any second of the day), and the media is the type of media, the possible values ​​of which can ONLY be "D", "A" or "V".

I need to write an sql instruction to select the registered user number of different carriers every day from 2013/01/01 to 2013/01/31. Thus, the desired result may be:

 2013/01/01 D 10 2013/01/01 A 31 2013/01/01 V 24 2013/01/02 D 9 2013/01/02 A 23 2013/01/02 V 14 .... 2013/01/31 D 11 2013/01/31 A 34 2013/01/31 V 22 

Can someone help me with this question? I have to give a BEST answer, which means that I can add an index if necessary, but I need the request to be efficient.

Currently, I created an index (reg_date, carrier) and used the following query:

 select FROM_UNIXTIME(reg_date, "%M %D %Y") as reg_day, carrier, count(carrier) as user_count from user_data where reg_date >= UNIX_TIMESTAMP('2013-01-01 00:00:00') and reg_date < UNIX_TIMESTAMP('2013-02-01 00:00:00') group by reg_day, carrier order by reg_date; 

Thanks!

Answer 1

If you cannot change the table (keeping separate dates will help a little), then only indexes, and then:

Create a composite index: carrier, reg_date , then group carrier, reg_date and order reg_date, carrier .

You can create another index only for timestamps (it may work better for WHERE caluse, depending on your number of records outside the scope).

In addition, you can use fully unix timestamps and then insert this as a subquery, and the external can hide timestamps until human-readable dates (thus, the conversion is done after the group, and not for each individual record).

Creating Indexes:

 CREATE INDEX bytime ON user_data (reg_date); CREATE INDEX daily_group ON user_data (carrier, reg_date); 

Query:

 SELECT FROM_UNIXTIME(reg_day, "%M %D %Y") AS reg_day , carrier , user_count FROM ( SELECT FLOOR(reg_date / (60 * 60 * 24)) AS reg_day , carrier , count(carrier) AS user_count FROM user_data WHERE reg_date >= UNIX_TIMESTAMP('2013-01-01 00:00:00') AND reg_date < UNIX_TIMESTAMP('2013-02-01 00:00:00') GROUP BY carrier, reg_day ORDER BY reg_day, carrier ) AS a;