Performance issue full index scan in mysql database

Question

I have a database with a table called QuizMatches. The table has the following structure:

CREATE TABLE `QuizMatches` (
  `QuizMatchesGuid` binary(16) NOT NULL,
  `DateStarted` datetime NOT NULL,
  `LatestChanged` datetime NOT NULL,
  `HostFBUserToken` varchar(250) NOT NULL,
  `GuestFBUserToken` varchar(250) NOT NULL,
  `ArrayOfQuestionIDs` varchar(200) NOT NULL,
  `ArrayOfQuestionResponseTimesAndAnswersHost` varchar(900) NOT NULL,
  `ArrayOfQuestionResponseTimesAndAnswersGuest` varchar(900) NOT NULL,
  `MatchFinished` int(1) NOT NULL DEFAULT '0',
  `Category` varchar(45) NOT NULL,
  `JsonQuestions` varchar(4000) NOT NULL DEFAULT '[]',
  `DateFinished` datetime NOT NULL,
  `LatestPushSentDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`QuizMatchesGuid`),
  KEY `HostFBUserTokenIX` (`HostFBUserToken`),
  KEY `GuestFBUserTokenIX` (`GuestFBUserToken`),
  KEY `MatchFinishedIX` (`MatchFinished`),
  KEY `LatestChangedIX` (`LatestChanged`),
  KEY `LatestPushSentDateIX` (`LatestPushSentDate`),
  KEY `DateFinishedIX` (`LatestChanged`,`HostFBUserToken`,`GuestFBUserToken`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

There is a large number of rows in this table and it is heavily used by multiple clients especially queries like the following are executed:

SELECT HEX(QuizMatchesGuid) AS QuizMatchesGuid, DateStarted,
    LatestChanged, HostFBUserToken, GuestFBUserToken,
    ArrayOfQuestionIDs, ArrayOfQuestionResponseTimesAndAnswersHost,
    ArrayOfQuestionResponseTimesAndAnswersGuest, JsonQuestions
FROM CrystalDBQuiz.QuizMatches
ORDER BY LatestChanged DESC
LIMIT 10 

The main problem seem to be that the database performs a full index scan. I have tried with different combinations of indexes but to no success.

If I run EXPLAIN on the above SELECT query I receive the following:

           id: 1
  select_type: SIMPLE
        table: 'QuizMatches'
         type: index
possible_keys: NULL
          key: 'LatestChangedIX'
      key_len: 8
          ref: NULL
         rows: 10
        Extra: 

Is there a way I can optimize SELECTS as the above example towards this database table?

Answer 1

If you are using the LIMIT statement for pagination I suggest you to use LatestChanged value for this due to ordering. So your query will turn to

SELECT HEX(QuizMatchesGuid) AS QuizMatchesGuid, DateStarted, LatestChanged, 
HostFBUserToken, GuestFBUserToken, ArrayOfQuestionIDs,
ArrayOfQuestionResponseTimesAndAnswersHost, 
ArrayOfQuestionResponseTimesAndAnswersGuest, JsonQuestions 
FROM CrystalDBQuiz.QuizMatches 
WHERE LatestChanged<[lastValue]
ORDER BY LatestChanged DESC
LIMIT 10