Mysql – ny way to get recently inserted value in a table without using aggregate functions

MySQL

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 .

Best Answer

Your existing index doesn't help you out at all, since you are actually asking for the last timestamp of every possible user. This means the only way for MySQL to answer this is to do a full table scan.

As @ypercube commented, an index on (user_id, login_time) is good for this kind of query - MySQL could utilize such an index without actually going to the table.

Adding an index to a table will indeed lock the table; but there are solutions to that as well. Take a look at oak-online-alter-table or pt-online-schema-change (disclaimer: I'm author of the first), both of which are scripts which allow online table alteration via simulation, triggers and incremental copy.

You might also want to solve this in a completely different way: create a new table like

CREATE TABLE last_user_login_time (
  user_id INT UNSIGNED PRIMARY KEY, 
  last_login_time DATETIME
);

And 

INSERT INTO last_user_login_time 
  VALUES (the_user_id, NOW()) 
ON DUPLICATE KEY UPDATE last_login_time=NOW()

to this table upon any new login. Thus, keeping the last login time per user at any given time. This means more writes (an additional write per login), but I suspect this table should be very small and the impact proportionally small. Reading from it, of course, is trivial.

Related Question