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 .

  • 1
    Use EXPLAIN for some insight into how MySQL is processing your query. Locking isn't a sufficient reason to avoid creating a necessary index. It doesn't take that long to index one column of 3 million rows. One alternative is to dump the database to a file, and load it onto a different machine. Build the index and run your report. – Mike Sherrill 'Cat Recall' Jan 16 '13 at 16:10
  • Creating an index on an integer field (I assume timestamp is managed as an integer) takes about two minutes (maybe less) on a low end machine with such a table size. You should reconsider this option. – jap1968 Jan 16 '13 at 17:05
  • What version of MySQL are you running ??? – RolandoMySQLDBA Jan 16 '13 at 17:22
  • ZmediaUserDetails is a table or view? – neo Jan 16 '13 at 17:30
  • 1
    An index on (user_id, login_time) would make this specific query much faster. – ypercubeᵀᴹ Jan 16 '13 at 17:40

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 (
  last_login_time DATETIME


INSERT INTO last_user_login_time 
  VALUES (the_user_id, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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