MySql takes too long with matching index I have the following Table with 774.462 rows.

Its running on mysql server 5.6.16, which has nothing other to do (running local)

But a simple query is running slow:

SELECT serial FROM `event` WHERE id=5586929 AND minute=71;
/* Affected rows: 0  Gefundene Zeilen: 1  Warnungen: 0  Dauer von 1 query: 0,422 sec. */


The explain seems to use the index



Why does this Query which perfecly matches my index take 400ms?

CREATE TABLE `event` (
    `serial` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `type` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `minute` BIGINT(20) NULL DEFAULT NULL,
    `running` TINYINT(1) NULL DEFAULT NULL,
    `home` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `away` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `starttime` BIGINT(20) NULL DEFAULT NULL,
    `period` INT(11) NULL DEFAULT NULL,
    `timeReference` BIGINT(20) NULL DEFAULT NULL,
    `timeOffset` BIGINT(20) NULL DEFAULT NULL,
    `goalsHome` INT(11) NULL DEFAULT NULL,
    `goalsAway` INT(11) NULL DEFAULT NULL,
    `twH` DOUBLE NULL DEFAULT NULL,
    `twX` DOUBLE NULL DEFAULT NULL,
    `twA` DOUBLE NULL DEFAULT NULL,
    `region` INT(11) NULL DEFAULT NULL,
    `redCardsTeam1` INT(11) NULL DEFAULT NULL,
    `redCardsTeam2` INT(11) NULL DEFAULT NULL,
    `yellowCardsTeam1` INT(11) NULL DEFAULT NULL,
    `yellowCardsTeam2` INT(11) NULL DEFAULT NULL,
    `offsidesTeam1` INT(11) NULL DEFAULT NULL,
    `offsidesTeam2` INT(11) NULL DEFAULT NULL,
    `goalKicksTeam1` INT(11) NULL DEFAULT NULL,
    `goalKicksTeam2` INT(11) NULL DEFAULT NULL,
    `throwInsTeam1` INT(11) NULL DEFAULT NULL,
    `throwInsTeam2` INT(11) NULL DEFAULT NULL,
    `penaltiesTeam1` INT(11) NULL DEFAULT NULL,
    `penaltiesTeam2` INT(11) NULL DEFAULT NULL,
    `substitutionsTeam1` INT(11) NULL DEFAULT NULL,
    `substitutionsTeam2` INT(11) NULL DEFAULT NULL,
    `cornersTeam1` INT(11) NULL DEFAULT NULL,
    `cornersTeam2` INT(11) NULL DEFAULT NULL,
    `freeKicksTeam1` INT(11) NULL DEFAULT NULL,
    `freeKicksTeam2` INT(11) NULL DEFAULT NULL,
    `overQuote` DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (`serial`),
    UNIQUE INDEX `id` (`id`, `minute`),
    INDEX `id_minute_index` (`id`, `minute`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1343356
;
I think that in your case a hidden type conversion is the cause:

`id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci'


but in query:

WHERE id=5586929


It means INT

Change this to:

WHERE id='5586929'


Also from my point of view (not related to SELECT performance, but generally):

UNIQUE INDEX `id` (`id`, `minute`),
INDEX `id_minute_index` (`id`, `minute`)


is a duplicated construction. UNIQUE is already an index. There is no need for the second one.