Take the 2-minute tour ×
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.

I'm using MySQL and have a table user_data like this:

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

The reg_data is the unix timestamp of the registration date, and the carrier is the type of carriers, the possible values of which could ONLY be 'D', 'A' or 'V'.

I need to write a sql statement to select the registered user number of different carriers on each day from 2013/01/01 to 2013/01/31. So the desirable result could 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 anyone help me with this question? I'm required to give the BEST answer, which means I can add index if necessary, but I need to keep the query efficient.

This is what I have right now:

select FLOOR((FROM_UNIXTIME(reg_date)) / 1000000) as reg_day, carrier, count(user_id) 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;

Thanks!

The question has been moved to here and please find all the updates in the link.

share|improve this question
    
BTW, it need to be done in ONE statement. –  Terry Mar 25 '13 at 7:45
    
Hello Terry, could you please add details about the current execution plan of your query, how long it takes, what indexes you currently have? It's hard to tell what needs tuning/tweaking without that type of information. –  Mat Mar 25 '13 at 10:26
    
The wording of this question makes me suspect that it is homework. If it is, then it should be tagged as such. –  Gord Thompson Mar 25 '13 at 13:50
    
Currently I have the index on (reg_date, carrier). –  Terry Mar 26 '13 at 1:26
    
Also, it is a interview question. –  Terry Mar 26 '13 at 1:29
add comment

1 Answer

You should make sure you have an index where reg_date is the first item covered, otherwise the query will table-scan the whole lot (an index on reg_date can be used for the limiting WHERE clause to reduce the rows touched).

As you are grouping by a result of a function, you will not be using any index based on the reg_date column which will make the group-by more expensive, though compared to the expense of not having an index the WHERE clause can use this will likely be a small difference) so you might consider storing the computed date for each row so you can index and filter/group on that directly.

(I assume you don't need rows returned with zero counts when there are no rows for a given carrier, which the current query won't return).

share|improve this answer
add comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.