I tried to get the users list who are all accessed our application in past 6 months . In our table , we are storing login_time , user_id ..etc . It's a huge table approximately it will contain over 3 million rows . We splitted up every month tables as like below format
TABLE_MONTH_YEAR . I am running this query in current month table only .
I tried this query .
select max(login_time) , user_id from ZmediaUserDetails group by user_id
user_id is an indexed column .
login_time is an timestamp .
I am running this query in production setup with master/slave setup . I ran this query in a slave machine . I waited for nearly a hour . Still it was not finished. I checked the slave status using
show slave status it shows
Seconds_Behind_Master: 4886 . So I killed that query .
I can't create an index in either master or slave . Because , It will lock the whole table .
Please give your suggestions .