What indexes to add on MySQL table 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 =&gt; #startHour# GROUP BY hour


Obtain server dependant statistics

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour =&gt; #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?

Thank you
What have you tried?  Are both of these queries running slowly?  Are there other operations which have become a problem?  Since you have a live system, rather than speculating about what might be causing a problem, you can add some profiling to be sure.

Your first query can be answered by an index on ( Hour, PuzzleScore ), in that order.  Your second would require adding Iip; since it should have excellent cardinality, I'd put it first: ( Iip, Hour, PuzzleScore ).  Check with EXPLAIN to confirm whether the indices are actually used; depending on row counts and cardinality, there may be no benefit.

Since you're querying on Hour either way, I'd consider clustering on that field (in conjunction with a unique field or combination of fields to ensure uniqueness, of course).

Caveat: I mostly work in SQL-Server, I'm no expert on MySQL.