[MySql] Query wordt traag door ORDER BY - Softwareontwikkeling

vrijdag 27 juli 2007 20:21

Acties:

Verwijderd

Topicstarter

Gegroet enzo,

Ik heb een probleem met een SQL query waar ik maar niet uit kom, het gaat om de volgende query:

SQL:

SELECT
    DateTime,
    Types.Name VictimShip,
    vchar.Name VictimName,
    vcorp.Name VictimCorp,
    kchar.Name FBName,
    kcorp.Name FBCorp,
    SolarSystems.Name SolarSystemName
FROM Kills
    INNER JOIN SolarSystems ON Kills.SolarSystemId = SolarSystems.SolarSystemId
    INNER JOIN Ships ON Kills.VictimShipId = Ships.TypeId
    INNER JOIN Types ON Ships.TypeId = Types.TypeId
    INNER JOIN Characters vchar ON Kills.VictimCharacterId = vchar.CharacterId
    INNER JOIN Corporations vcorp ON Kills.VictimCorporationId = vcorp.CorporationId
    INNER JOIN Killers ON Kills.FinalBlowId = Killers.KillerId
    INNER JOIN Characters kchar ON Killers.CharacterId = kchar.CharacterId
    INNER JOIN Corporations kcorp ON Killers.CorporationId = kcorp.CorporationId
ORDER BY DateTime

Het duurt tussen de 1,5 en 2 seconden om die query uit te voeren maar als ik de "ORDER BY" weg haal dan duurt het nog maar iets van 0.06 seconden om de query uit te voeren.

De "Kills" tabel heeft wel een flink aantal records (ong 14000) maar volgens mij zou dat niet moeten uitmaken voor het orderen want als ik de volgende query uitvoer gaat het wel snel:

SQL:

1	SELECT * FROM Kills ORDER BY DateTime

Het moet dus een combinatie zijn tussen de joins en de order by, het probleem doet zich overigens ook voor als ik op iets anders probeer te sorteren.

Ik heb OPTIMIZE en ANALYZE uitgevoerd op alle tabellen maar het haalde weinig uit, heb ook geprobeerd verschillende indexes te gebruik maar ook dat maakte weinig verschil, ook LIMIT gebruiken haalt niks uit.

Hier onder alle relevante creates:

SQL:

DROP TABLE IF EXISTS `Killboard`.`Kills`;
CREATE TABLE `Killboard`.`Kills` (
  `KillId` INT NOT NULL AUTO_INCREMENT,
  `SolarSystemId` INT NOT NULL,
  `VictimCharacterId` INT NOT NULL,
  `VictimCorporationId` INT NOT NULL,
  `VictimAllianceId` INT NOT NULL,
  `VictimShipId` INT NOT NULL,
  `FinalBlowId` INT NOT NULL,
  `DateTime` DATETIME NOT NULL,
  PRIMARY KEY (`KillId`),
  INDEX (`SolarSystemId`),
  INDEX (`VictimCharacterId`),
  INDEX (`VictimCorporationId`),
  INDEX (`VictimAllianceId`),
  INDEX (`VictimShipId`),
  INDEX (`FinalBlowId`),
  INDEX (`DateTime`),
  CONSTRAINT `FK_Kills_SolarSystems` FOREIGN KEY (`SolarSystemId`) REFERENCES `SolarSystems` (`SolarSystemId`),
  CONSTRAINT `FK_Kills_Characters` FOREIGN KEY (`VictimCharacterId`) REFERENCES `Characters` (`CharacterId`),
  CONSTRAINT `FK_Kills_Corporations` FOREIGN KEY (`VictimCorporationId`) REFERENCES `Corporations` (`CorporationId`),
  CONSTRAINT `FK_Kills_Alliances` FOREIGN KEY (`VictimAllianceId`) REFERENCES `Alliances` (`AllianceId`),
  CONSTRAINT `FK_Kills_Types` FOREIGN KEY (`VictimShipId`) REFERENCES `Types` (`TypeId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `Killboard`.`Alliances`;
CREATE TABLE `Killboard`.`Alliances` (
  `AllianceId` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`AllianceId`),
  UNIQUE INDEX (`Name`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `Killboard`.`Characters`;
CREATE TABLE `Killboard`.`Characters` (
  `CharacterId` INT NOT NULL AUTO_INCREMENT,
  `CorporationId` INT NOT NULL,
  `Name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`CharacterId`),
  INDEX (`CorporationId`),
  UNIQUE INDEX (`Name`),
  CONSTRAINT `FK_Characters_Corporations` FOREIGN KEY (`CorporationId`) REFERENCES `Corporations` (`CorporationId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `Killboard`.`Corporations`;
CREATE TABLE `Killboard`.`Corporations` (
  `CorporationId` INT NOT NULL AUTO_INCREMENT,
  `AllianceId` INT NOT NULL,
  `Name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`CorporationId`),
  INDEX (`AllianceId`),
  UNIQUE INDEX (`Name`),
  CONSTRAINT `FK_Corporations_Alliances` FOREIGN KEY (`AllianceId`) REFERENCES `Alliances` (`AllianceId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `Killboard`.`Killers`;
CREATE TABLE `Killboard`.`Killers` (
  `KillerId` INT NOT NULL AUTO_INCREMENT,
  `KillId` INT NOT NULL,
  `CharacterId` INT NOT NULL,
  `CorporationId` INT NOT NULL,
  `AllianceId` INT NOT NULL,
  `ShipId` INT NOT NULL,
  `WeaponId` INT NOT NULL,
  PRIMARY KEY (`KillerId`),
  INDEX (`KillId`),
  INDEX (`CharacterId`),
  INDEX (`CorporationId`),
  INDEX (`AllianceId`),
  INDEX (`ShipId`),
  INDEX (`WeaponId`),
  CONSTRAINT `FK_Killers_Kills` FOREIGN KEY (`KillId`) REFERENCES `Kills` (`KillId`),
  CONSTRAINT `FK_Killers_Characters` FOREIGN KEY (`CharacterId`) REFERENCES `Characters` (`CharacterId`),
  CONSTRAINT `FK_Killers_Corporations` FOREIGN KEY (`CorporationId`) REFERENCES `Corporations` (`CorporationId`),
  CONSTRAINT `FK_Killers_Alliances` FOREIGN KEY (`AllianceId`) REFERENCES `Alliances` (`AllianceId`),
  CONSTRAINT `FK_Killers_Types_Ships` FOREIGN KEY (`ShipId`) REFERENCES `Types` (`TypeId`),
  CONSTRAINT `FK_Killers_Types_Weapons` FOREIGN KEY (`WeaponId`) REFERENCES `Types` (`TypeId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `Killboard`.`Ships`;
CREATE TABLE `Killboard`.`Ships` (
  `TypeId` INT NOT NULL,
  `ShipClassId` INT NOT NULL,
  PRIMARY KEY (`TypeId`),
  INDEX (`ShipClassId`),
  CONSTRAINT `FK_Ships_Types` FOREIGN KEY (`TypeId`) REFERENCES `Types` (`TypeId`),
  CONSTRAINT `FK_Ships_ShipClasses` FOREIGN KEY (`ShipClassId`) REFERENCES `ShipClasses` (`ShipClassId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `Killboard`.`Types`;
CREATE TABLE `Killboard`.`Types` (
  `TypeId` INT NOT NULL,
  `Name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`TypeId`),
  UNIQUE INDEX (`Name`)
)
ENGINE = INNODB;

[ Voor 11% gewijzigd door Verwijderd op 27-07-2007 20:37 ]

vrijdag 27 juli 2007 20:34

Acties:

whoami

Ik snap het nut niet van die order-by ? Voor zover ik kan zien, heb je helemaal geen veld dat 'DateTime' heet in één van je tabellen ?

https://fgheysels.github.io/

vrijdag 27 juli 2007 20:38

Acties:

Verwijderd

Topicstarter

Oopsie, ik was 1 tabel vergeten te posten (de Kills tabel), hij staat er nu bij

vrijdag 27 juli 2007 20:46

Acties:

SIMPY.be

Verwijderd schreef op vrijdag 27 juli 2007 @ 20:21:
Gegroet enzo,

Ik heb een probleem met een SQL query waar ik maar niet uit kom, het gaat om de volgende query:

helpt het niet om de index te definiëren op de kolom datetime?

[ Voor 90% gewijzigd door SIMPY.be op 27-07-2007 20:47 ]

vrijdag 27 juli 2007 20:48

Acties:

Confusion

Fallen from grace

MySQL gebruikt maximaal 1 index per tabel. Als je Kills op VictimShipID joined, dan wordt de index op VictimShipID al gebruikt en de index op DateTime dus niet. Dat betekent dat je voor de order by een volledige losse ordeningsslag nodig hebt. Probeer eens een gecombineerde index over VictimShipID en DateTime te leggen. Welke index er wordt gebruikt (if any) kan je overigens uitvinden door EXPLAIN voor de query te zetten en de output van dat commando goed te bestuderen.

[ Voor 20% gewijzigd door Confusion op 27-07-2007 20:49 ]

Wie trösten wir uns, die Mörder aller Mörder?

vrijdag 27 juli 2007 21:53

Acties:

Verwijderd

Topicstarter

Bedank voor de snelle replies, ik ben er zojuist mee bezig geweest maar het lukt nog niet zoals ik wil.

Ik heb een index gemaakt over VictimShipId en DateTime maar toen ik de EXPLAIN bekeek gebruike hij toch een andere index

Ik join bijna alle foreign keys van de Kills table wel SolarSystemId, VictimShipId, VictimCharacterId, VictimCorporationId en FinalBlowId worden allemaal op iets gejoined dus ik dacht dan maak ik gewoon een index met al de columns en DateTime erbij maar toen pakte hij die index ook weer niet

vrijdag 27 juli 2007 22:35

Acties:

Verwijderd

Om je even snel op weg te helpen zonder dat ik me er in verdiept heb:

Gebruik om zelf te bepalen welke index-en de engine moet gebruiken: USE INDEX

http://64.233.183.104/sea...&hl=nl&ct=clnk&cd=1&gl=nl

zaterdag 28 juli 2007 10:12

Acties:

Confusion

Fallen from grace

Heb je na het veranderen van de indexen een nieuwe ANALYZE TABLE en OPTIMIZE TABLE uitgevoerd? Dat is regelmatig nodig om te zorgen dat de nieuwe index ook echt gebruikt wordt.

Wie trösten wir uns, die Mörder aller Mörder?

zaterdag 28 juli 2007 16:03

Acties:

Verwijderd

Topicstarter

Ik denk dat ik nu door heb hoe het werkt (kan zijn dat ik het helemaal mis heb

)

Uit de Kills tabel selecteer ik eigenlijk alles waardoor er geen index nodig is behalve dan om te sorteren, de DateTime index dus.

Als ik een andere tabel join op Kills dan heb ik een index nodig uit de tabel die gejoined wordt en niet uit de tabel waarop gejoined wordt. Dus voor de duidelijkheid, als ik de tabel Characters wil joinen op Kills.VictimCharacterId dan heb ik een index nodig op Characters.CharacterId en niet op Kills.VictimCharacterId

Nadat ik daar achter was keek ik nog is goed naar de explain van de query en zag dat ipv de DateTime index de CorporationId index van kills gebruikt werd (geen idee waarom), dus ik forceerde de DateTime index door middel van "USE INDEX" en het werkte. Het duurt nu 0.003 seconden om de query uit te voeren

Maargoed zo lijkt het te werken maar mischien heb ik het wel helemaal bij het verkeerde eind.