Performance issue full index scan in mysql database

Viewed 2998

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?

Neor profiler screenshot

1 Answers
Related