Slow playlog overview when you have a lot of entrys

[keldnorman]

I think there is a need for an index in the mysql database for kippo-graph when you get a lot of playlog entrys. My page http://x.x.x.x/kippo-graph/kippo-playlog.php is loading for 10 minutes+ before it shows the list of playlogs ( I am using cowrie by the way and upgraded the mysql server to ver 5.6 on ubuntu 14.04.5 LTS, Trusty Tahr 4 x Intel(R) Xeon(R) CPU X5570 @ 2.93GHz and 2Gb memory) Kippo-graph source: Git clone from Jan 12 2017

Currently the mysql db is holding: Total login attempts 107.248 Distinct source IP addresses 14.200

I can see that the Mysql database is 100% busy handeling that request (looks like sql with join ) - could you help us figure out what index is needed to speed things up ?

I think this is the query that needs some helping index in the DB

Query, Full Table Scan, Executed (#), Errors (#), Warnings (#), Total Time, Max Time, Avg Time, Rows Sent (#), Avg. Rows Sent (#), Rows Scanned (#), Avg. Rows Scanned (#), Temp. Tables (#), Temp. Disk Tables (#), Rows Sorted (#), Sort Merge Passes (#), Digest

SELECT * FROM ( SELECT ttylog . session , auth . timestamp , ROUND ( size / ?, ... ) AS size , COUNT ( input ) AS input FROM ttylog JOIN auth ON ttylog . session = auth . session JOIN input ON ttylog . session = input . session WHERE auth . success = ? GROUP BY ttylog . session ORDER BY auth . timestamp ASC ) s WHERE size > ? , *, 7, 0, 0, 40717537067.90, 10077257004.40, 5816791009.70, 140473, 20068.0, 3812953, 544708.0, 14, 0, 144174, 0, 9f0698b3ea97f01650e20f9f519ae676

Best regards Keld Norman