I've been trying to optimize my table, but am yet unable to get the desired results.
CREATE TABLE IF NOT EXISTS `puzzles` ( `puzzleID` int(7) NOT NULL, `puzzleMask` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `puzzleScore` int(7) NOT NULL, `time` int(11) NOT NULL DEFAULT '0', `hour` int(11) NOT NULL DEFAULT '0', `ip` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, `Iip` int(11) unsigned NOT NULL DEFAULT '1', PRIMARY KEY (`puzzleID`, `puzzleMask`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
The combination of ID and Mask must be unique, whereas ID is a random integer and Mask a combination of numbers with a max length of 30. Time is the unix timestamp and hour is "time - (time % 3600)" (the hour in which the row was submitted). ip is the IP address of a server (about 20 servers) and Iip is INET_ATON( ip ).
These 20 servers submit puzzle information to this database. I would like to show live statistics of the submitted information (5-10 records per second).
For that, I use two main queries which need to be as optimal as possible:
Obtain total statistics
SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# GROUP BY hour
Obtain server dependant statistics
SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# AND Iip = INET_ATON("#IPAddress#") GROUP BY hour
My question is, what indexes to use? I've tried many things now, but with the database rapidly increasing the time to trial and error is over... for example, do I need a seperate index for puzzleScore, since I want the max? or do I need to combine it in another index?
Also, is it true that using a primary key (puzzleID and puzzleMask) in combination with INSERT IGNORE INTO... is better for the performance, than using an id with autoincrement and first perform a SELECT to see if the record exists and than insert?