What you have (a little more readable):
SELECT gi.longip, cl.locId,
cl.city, cl.region,
count(*) as loccount
FROM
( SELECT distinct longip
from whyu.stats
WHERE longip!=0
) as gi
STRAIGHT_JOIN geoipdb.cityblocks AS cb ON g.longip BETWEEN cb.startIpNum AND endIpNum
INNER JOIN geoipdb.citylocation AS cl ON cb.locId = cl.locId
GROUP BY cl.locId
Are there any overlaps in start/endIpNum? If not, we may be able to turn this Order(N*N) STRAIGHT_JOIN into an Order(N) subquery.
To assist in analyzing slow SELECTs, please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
See if any of these help:
http://forums.mysql.com/read.php?10,356125 (update big table cause an excution time out)
http://forums.mysql.com/read.php?10,293341 (Help with the best table layout for message board)
http://forums.mysql.com/read.php?10,278476 (Can a Mysql MyISAM or InnoDB table benefit from the STRIPE feature of disk device?)
http://forums.mysql.com/read.php?10,278480 (Can a Mysql MyISAM or a InnoDB table benefit from a set of multi-thread and multi-disk I/O device?)
http://forums.mysql.com/read.php?52,271268 (Checking for exact match in row)
http://forums.mysql.com/read.php?10,255092 (What data type should I use and what storage engine?)
Are you willing to break geoipdb.cityblocks into 32 tables?