Getting "Using where; Using index" in Extra column of EXPLAIN when only querying indexed f-CodePudding

When running the following query:

EXPLAIN SELECT 
    belgarath.match_oc_history.id_, belgarath.match_oc_history.tour_id
FROM
    belgarath.match_oc_history
        JOIN
    belgarath.tournament_oc ON belgarath.tournament_oc.tour_id = belgarath.match_oc_history.tour_id
        AND belgarath.tournament_oc.orig_id = belgarath.match_oc_history.tournament_oc_orig_id;

I get the following table:

 ---- ------------- ------------------ ------------ ------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ --------- ----------------------------------------------------------------- ------- ---------- -------------------------- 
| id | select_type |      table       | partitions | type  |                                                                                          possible_keys                                                                                           |                         key                          | key_len |                               ref                               | rows  | filtered |          Extra           |
 ---- ------------- ------------------ ------------ ------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ --------- ----------------------------------------------------------------- ------- ---------- -------------------------- 
|  1 | SIMPLE      | tournament_oc    | NULL       | index | uq__tournament_oc__tour_id__orig_id,ix__tournament_oc__tour_id,ix__tournament_oc__orig_id                                                                                                        | uq__tournament_oc__tour_id__orig_id                  |       6 | NULL                                                            | 26550 |      100 | Using where; Using index |
|  1 | SIMPLE      | match_oc_history | NULL       | ref   | ix__match_oc_history__five_keys,ix__match_oc_history__tour_id,fk__match_oc_history__player_oc_p1_idx,fk__match_oc_history__player_oc_p2_idx,fk__match_oc_history__tour_id__tournament_oc_orig_id | fk__match_oc_history__tour_id__tournament_oc_orig_id |       5 | belgarath.tournament_oc.tour_id,belgarath.tournament_oc.orig_id |    54 |      100 | Using index              |
 ---- ------------- ------------------ ------------ ------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------ --------- ----------------------------------------------------------------- ------- ---------- --------------------------

My understanding from reading around is that if a query is only working with indexed fields then it shouldn't need to use where. Is this correct?

If yes, then why am I seeing it pop up in the EXPLAIN table?

If no, should I be concerned about the use of where in this instance? I've always assumed that where is slower than index...

Table compositions:

CREATE TABLE `tournament_oc` (
  `updated` timestamp NULL DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `id_` int NOT NULL AUTO_INCREMENT,
  `tour_id` tinyint DEFAULT NULL,
  `orig_id` int NOT NULL,
  PRIMARY KEY (`id_`),
  UNIQUE KEY `uq__tournament_oc__tour_id__orig_id` (`tour_id`,`orig_id`),
  KEY `ix__tournament_oc__tour_id` (`tour_id`),
  KEY `ix__tournament_oc__orig_id` (`orig_id`),
  CONSTRAINT `fk__tournament_oc__tour_id` FOREIGN KEY (`tour_id`) REFERENCES `tour` (`id_`)
) ENGINE=InnoDB AUTO_INCREMENT=27788 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


CREATE TABLE `match_oc_history` (
  `updated` timestamp NULL DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `id_` int NOT NULL AUTO_INCREMENT,
  `tour_id` tinyint NOT NULL,
  `tournament_oc_orig_id` int NOT NULL,
  `round_oc_id` tinyint NOT NULL,
  `player_oc_orig_id_p1` int NOT NULL,
  `player_oc_orig_id_p2` int NOT NULL,
  PRIMARY KEY (`id_`),
  KEY `ix__match_oc_history__five_keys` (`tour_id`,`tournament_oc_orig_id`,`round_oc_id`,`player_oc_orig_id_p1`,`player_oc_orig_id_p2`),
  KEY `ix__match_oc_history__round_oc_id` (`round_oc_id`),
  KEY `ix__match_oc_history__tour_id` (`tour_id`),
  KEY `fk__match_oc_history__player_oc_p1_idx` (`tour_id`,`player_oc_orig_id_p1`),
  KEY `fk__match_oc_history__player_oc_p2_idx` (`tour_id`,`player_oc_orig_id_p2`),
  KEY `fk__match_oc_history__tour_id__tournament_oc_orig_id` (`tour_id`,`tournament_oc_orig_id`),
  CONSTRAINT `fk__match_oc_history__player_oc_p1` FOREIGN KEY (`tour_id`, `player_oc_orig_id_p1`) REFERENCES `player_oc` (`tour_id`, `orig_id`),
  CONSTRAINT `fk__match_oc_history__player_oc_p2` FOREIGN KEY (`tour_id`, `player_oc_orig_id_p2`) REFERENCES `player_oc` (`tour_id`, `orig_id`),
  CONSTRAINT `fk__match_oc_history__round_oc_id` FOREIGN KEY (`round_oc_id`) REFERENCES `round_oc` (`id_`),
  CONSTRAINT `fk__match_oc_history__tour_id` FOREIGN KEY (`tour_id`) REFERENCES `tour` (`id_`),
  CONSTRAINT `fk__match_oc_history__tournament_oc` FOREIGN KEY (`tour_id`, `tournament_oc_orig_id`) REFERENCES `tournament_oc` (`tour_id`, `orig_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1516084 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

As per comments:

SHOW WARNINGS statement response:

/* select#1 */ select `belgarath`.`match_oc_history`.`id_` AS `id_`,`belgarath`.`match_oc_history`.`tour_id` AS `tour_id` from `belgarath`.`match_oc_history` join `belgarath`.`tournament_oc` where ((`belgarath`.`match_oc_history`.`tournament_oc_orig_id` = `belgarath`.`tournament_oc`.`orig_id`) and (`belgarath`.`match_oc_history`.`tour_id` = `belgarath`.`tournament_oc`.`tour_id`))

CodePudding user response：

Using index means that the entire query (at least for the table in question) does not need any columns other that what is in the INDEX. This is faster than having to bounce between the Index's BTree and the data's BTree.
The Optimizer is free to reorder JOINed tables as it sees fit. The EXPLAIN reflects what the Optimizer decided.
The "warning" shows how it decided to execute the query.
OR is often turned into WHERE when parsing. (You were correct to use ON, since that is how the tables are related.)
Without a true WHERE, the Optimizer [usually] picks the smaller table, then does a "nested loop join" (NLJ) to reach into the next table. And this is often the faster way to perform the query. I see no need for STRAIGHT_JOIN; trust the Optimizer.
You have optimal composite indexes to let the Optimizer pick either table first, optimally.
Why do you think that the 'wrong' table went first?
"Using where" does not mean much; ignore it.
"where is slower than index" -- This does not make sense. A WHERE clause with a suitable index will make the query run faster. An ON clause almost always needs an index to run faster. (What you have is effectively ON.)