JOIN dauert mehrere Minuten (Problem: fehlende FKs ?)

[Karlo]

Wie sind die Fremdschlüssel denn realisiert? Am besten, du postest mal das CREATE-Statement der drei Tabellen. Außerdem wären ein, zwei Demo-Datensätze, die darin gespeichert sind, nicht schlecht, so zur Anschauung.

Ok hier das Create Statement:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;  
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_keyCHECKS, FOREIGN_KEY_CmydbHECKS=0;  
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';  
  
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;  
USE `mydb`;  
  
-- -----------------------------------------------------  
-- Table `mydb`.`keys`  
-- -----------------------------------------------------  
CREATE  TABLE IF NOT EXISTS `mydb`.`keys` (  
  `key` VARCHAR(12) NOT NULL ,  
  PRIMARY KEY (`key`) )  
ENGINE = MyISAM;  
  
  
-- -----------------------------------------------------  
-- Table `mydb`.`name`  
-- -----------------------------------------------------  
CREATE  TABLE IF NOT EXISTS `mydb`.`name` (  
  `id` INT NOT NULL AUTO_INCREMENT ,  
  `value` VARCHAR(300) NOT NULL ,  
  `key` VARCHAR(12) NOT NULL ,  
  INDEX `fk_name_keys` (`key` ASC) ,  
  PRIMARY KEY (`id`) ,  
  CONSTRAINT `fk_name_keys`  
    FOREIGN KEY (`key` )  
    REFERENCES `mydb`.`keys` (`key` )  
    ON DELETE NO ACTION  
    ON UPDATE NO ACTION)  
ENGINE = MyISAM;  
  
  
-- -----------------------------------------------------  
-- Table `mydb`.`ort`  
-- -----------------------------------------------------  
CREATE  TABLE IF NOT EXISTS `mydb`.`ort` (  
  `id` INT NOT NULL AUTO_INCREMENT ,  
  `value` VARCHAR(12) NOT NULL ,  
  `key` VARCHAR(12) NOT NULL ,  
  INDEX `fk_ort_keys` (`key` ASC) ,  
  PRIMARY KEY (`id`) ,  
  CONSTRAINT `fk_ort_keys`  
    FOREIGN KEY (`key` )  
    REFERENCES `mydb`.`keys` (`key` )  
    ON DELETE NO ACTION  
    ON UPDATE NO ACTION)  
ENGINE = MyISAM;  
  
  
-- -----------------------------------------------------  
-- Table `mydb`.`identity_relation`  
-- -----------------------------------------------------  
CREATE  TABLE IF NOT EXISTS `mydb`.`identity_relation` (  
  `id` INT NOT NULL AUTO_INCREMENT ,  
  `value` VARCHAR(300) NOT NULL ,  
  `key` VARCHAR(12) NULL ,  
  INDEX `fk_identity_relation_keys` (`key` ASC) ,  
  PRIMARY KEY (`id`) ,  
  CONSTRAINT `fk_identity_relation_keys`  
    FOREIGN KEY (`key` )  
    REFERENCES `mydb`.`keys` (`key` )  
    ON DELETE NO ACTION  
    ON UPDATE NO ACTION)  
ENGINE = MyISAM;

Demodatensätze:

Tabelle keys:
keys
0001AF
000F19
001B9E

Tabelle name:
id        value        key
1         Mustermann   0001AF
2         Huber        000F19
3         Huber jun.   000F19
4         Meier        001B9E

Tabelle ort:
id        value        key
1         Hamburg      0001AF
2         Berlin       000F19
3         Berlin       000F19
4         Dresden      001B9E

Die Tabelle keys hab ich deswegen angelegt, um eine Tabelle zu haben, die alle keys genau einmal enthält.