Use the DATETIME datatype instead of this:
`date_time` varchar(23) NOT NULL default '',
I suspect this is giving the wrong answer:
date_time < a.date_time + interval 1 second
The second of these is redundant and can be DROPped:
UNIQUE KEY `traffic_id_date_time`( `network_traffic_id` , `date_time` ),
KEY `network_traffic_id`( `network_traffic_id` ),
innodb_buffer_pool_size is 2G, I assume all your tables are InnoDB. If so, then caching is no the issue. With 20GB of RAM, the buffer pool could be made much larger (but this may not help the current issue).
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
It's a "correlated subquery" (see a.network_traffic_id), so it cannot be turned into a temp table without some further thought.
Why
date_time < a.date_time + interval 1 second
instead of
date_time <= a.date_time
?
With that change, there is hope for the 'group by trick':
http://forums.mysql.com/read.php?10,428347 (Last or Max?)
http://forums.mysql.com/read.php?10,400775 (Assistance required for query with subquery)
http://forums.mysql.com/read.php?10,396680 (Slow Query)
http://forums.mysql.com/read.php?10,394154 (Require help to formulate a query.)
http://forums.mysql.com/read.php?20,389565 (query returns unwanted duplicates)
http://forums.mysql.com/read.php?10,387554 (aggreate data)
http://forums.mysql.com/read.php?10,373984 (select max date in Inner Join)
http://forums.mysql.com/read.php?20,291994 (create function help)
http://forums.mysql.com/read.php?10,290067 (Help with minimum/group by)
http://forums.mysql.com/read.php?10,284734 (max function)
http://forums.mysql.com/read.php?10,280124 (limiting rows returned)
http://forums.mysql.com/read.php?52,272271 (Subquery and JOIN's)
http://forums.mysql.com/read.php?10,266585 (How does group by algorithm works?)
http://forums.mysql.com/read.php?10,250033 (Listing threads by newest posts?)
http://forums.mysql.com/read.php?10,236805 (Complex Select with Min/Max Syntax)