I've been trying to optimize my table, but am yet unable to get the desired results.

  `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`)

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?

Thank you

  • Would it be better to have an column "id" with primary, and use UNIQUE on puzzleID and puzzleMask?
    – Jeffrey
    Commented Jul 29, 2013 at 14:04
  • 1
    For your stats queries, try the two composite indexes: (hour, puzzleScore) and (Iip, hour, puzzleScore) Commented Jul 29, 2013 at 14:29

1 Answer 1


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.

  • Addendum to my MySQL ignorance: it looks like MySQL does not support indices with included non-key fields; if I'm wrong, then by all means use this feature for PuzzleScore. Also, if supported on MySQL, an instantiated view on one or both queries would definitely help reads, at the cost of slower writes. Commented Jul 29, 2013 at 14:13
  • MySQL does not have indexes with INCLUDE() like SQL-Server but I don't think it matters in this case. Commented Jul 29, 2013 at 14:37
  • Again I am amazed by the power of MySQL. The indexes I added did all not include the puzzleScore in it, which seems to be a key element! The first querie is about 100 times quicker, whereas the second 20-40 times and both under a tenth of a second. Thank you both very much!
    – Jeffrey
    Commented Jul 29, 2013 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.