I have a very large number (millions) of messages, each labeled with the unixtime it was sent in a SQLite database. Each message has its own userid, for the user that have sent it. I want to know what's the max number of messages that is sent within an 24hr timeslot for each user. The 24hr timeslot is defined as the time from one message to another. So if there are five messages, where the 5th one is sent 24 hours after the first one, 5 is the number I want.
I have code that gives me this frequency, but the problem is that the running time of this is just too large - and I guess thats partly because of my not-optimal code, and too high complexity. How can I optimize this?
con = lite.connect(databasepath) userID =  messages =  messageFrequency =  with con: cur = con.cursor() #Get all UserID cur.execute('SELECT DISTINCT userid FROM MessageType1') userID = cur.fetchall() userID = [x for x in userID] #For each UserID for user in userID: messageFrequency.append(0) #Get all MSG with UserID = UserID sorted by UNIXTIME cur.execute('SELECT unixtime FROM MessageType1 WHERE userID ='+str(user)+' ORDER BY unixtime asc') Messages = cur.fetchall() Messages = [x for x in Messages] length = len(Messages) #Loop through every MSG for message in Messages: index = Messages.index(message) nextmessage = Messages[index+1] frequency = 0 #Loop through every message that is within 24 hours while nextmessage < message+(24*60*60) and index<length-1: #Count the number of occurences frequency += 1 index += 1 nextmessage = Messages[index] #Add best benchmark for every message to a list if messageFrequency[-1]<frequency: messageFrequency[-1] = frequency