Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Thank you

share|improve this question
    
Would it be better to have an column "id" with primary, and use UNIQUE on puzzleID and puzzleMask? –  Jeffrey Jul 29 '13 at 14:04
1  
For your stats queries, try the two composite indexes: (hour, puzzleScore) and (Iip, hour, puzzleScore) –  ypercube Jul 29 '13 at 14:29
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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. –  Jon of All Trades Jul 29 '13 at 14:13
    
MySQL does not have indexes with INCLUDE() like SQL-Server but I don't think it matters in this case. –  ypercube Jul 29 '13 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 Jul 29 '13 at 15:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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