0

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

explain

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
;
  • 1
    Why is the id a VARCHAR if you are storing only numbers? – ypercubeᵀᴹ Nov 16 '16 at 12:16
  • 1
    Most likely you can also reduce some datatypes. Bigint for minute, for example. Just saying... – tombom Nov 16 '16 at 13:00
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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