Résolu requete très lente mysql

[overclockthom]

Bonsoir,

J'ai quelques problèmes de lenteur avec MySQL. J'ai pour projet de générer quelques statistiques à partir des données d'une jeu par navigateur.

Le jeu met à disposition un fichier .sql qui me permet de générer tous les jours une table de ce type là avec pour le moment 23k entrées qui vont grimper assez vite vers 40k je pense :

CREATE TABLE IF NOT EXISTS `x_world` (
  `id` int(9) unsigned NOT NULL DEFAULT '0',
  `x` smallint(3) NOT NULL DEFAULT '0',
  `y` smallint(3) NOT NULL DEFAULT '0',
  `tid` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `vid` int(9) unsigned NOT NULL DEFAULT '0',
  `village` varchar(20) NOT NULL DEFAULT '',
  `uid` int(9) NOT NULL DEFAULT '0',
  `player` varchar(20) NOT NULL DEFAULT '',
  `aid` int(9) unsigned NOT NULL DEFAULT '0',
  `alliance` varchar(8) NOT NULL DEFAULT '',
  `population` smallint(5) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Je fais tourner ça en local pour le moment, après ça ira peut-être sur un petit hébergement pas cher.
Mon PC est relativement performant (i5 540 2x 2.5GHz, 8Go de RAM, wamp x64)

Premier problème :
la récupération des données

Le fichier .sql se présente sous la forme de 22k lignes avec sur chaque ligne une requête INSERT INTO.

12 minutes pour charger les valeurs en utilisant l'importation via la console mysql
c'est un temps un peu affreux je trouve.

Je dois exécuter l'importation une seule fois par jour, ça peut prendre un peu de temps, mais pas 12 minutes.
De plus je voudrais à terme faire mes stats pour chaque monde du jeu (à la louche une 10aine) donc pas possible de prendre autant de temps pour chaque importation.

Second problème :
Et là quand on commence à faire des requêtes sérieuses pour générer les tables qui serviront aux stats à partir des archives et de la nouvelle table c'est la galère, je n'ai même pas eu le courage de laisser aller au bout de l'exécution de toutes les requêtes.

Je génère une unique table finale assez lourde et très riche d'informations (avec une info redondante la 'poptot' d'un joueur) afin de ne pas avoir à faire des jointures par la suite lors de l'utilisation de ces données. Je n'ai que des lectures à faire et donc ça me permettra (je pense) d'accélérer mes requêtes.

-- creation de la table village :
-- ------------

-- la liste des vivi + évolution de pop
DROP TABLE IF EXISTS vivitemp ;

CREATE TABLE vivitemp AS
(
SELECT

x_world.id AS id,
x_world.vid AS vid,
x_world.village AS village,
x_world.x AS x,
x_world.y AS y,
x_world.tid AS tid,
x_world.uid AS uid,
x_world.player AS player,
x_world.aid AS aid,
x_world.alliance AS alliance,
x_world.population AS population,
(CAST(`x_world`.`population` AS signed) - CAST(`x_world08022013`.`population` AS signed)) AS popevo5,
(CAST(`x_world`.`population` AS signed) - CAST(`x_world06022013`.`population` AS signed)) AS popevo7

FROM `x_world` LEFT OUTER JOIN `x_world08022013`
ON `x_world`.`vid` = `x_world08022013`.`vid` 
LEFT OUTER JOIN `x_world06022013`
ON `x_world`.`vid` = `x_world06022013`.`vid` 
);

ALTER TABLE `vivitemp` ADD UNIQUE (`vid`);

UPDATE `vivitemp` SET `vivitemp`.`popevo5` = `vivitemp`.`population` WHERE `vivitemp`.`popevo5` IS NULL ;
UPDATE `vivitemp` SET `vivitemp`.`popevo7` = `vivitemp`.`population` WHERE `vivitemp`.`popevo7` IS NULL ;

-- les joueurs pour avoir la pop totale

DROP TABLE IF EXISTS joueurstemp ;

CREATE TABLE joueurstemp AS
(

SELECT
x_world.uid AS uid2,
SUM(x_world.population) AS poptot

FROM `x_world`

GROUP BY uid2
);

ALTER TABLE `joueurstemp` ADD UNIQUE (`uid2`);

-- table finale

DROP TABLE IF EXISTS villages ;

CREATE TABLE villages AS
(
SELECT *

FROM
vivitemp, joueurstemp

WHERE
vivitemp.uid=joueurstemp.uid2
);

ALTER TABLE `villages` ADD UNIQUE (`vid`);

La première requête (CREATE TABLE vivitemp) en remplaçant les 3 tables différentes (xworld, xworld080213 et xworld060213) par 3 fois la même (3x xworld) met plus d'1/2h pour s’exécuter. J'ai testé rapidement avec MS SQL Server 2008 et 18 secondes pour la même requête....

Ai-je raté quelque chose dans la config de MySQL pour qu'il y ai une différence pareille ?
Comment puis-je optimiser mon histoire pour rester si possible avec MySQL (plus pratique à utiliser et surtout possibilité de passer le tout sur linux pour une utilisation en prod) ?

Merci d'avance pour vos conseils et vos réponses !

 

[overclockthom]

pour l'importation problème résolu j'ai refait des test et je n'ai que 1 à 2 minutes d'import

pour la requête avec jointure c'est bon aussi, pour optimiser la chose j'ai utilisé l'id qui est la primary key au lieu de 'vid' qui n'est pas du tout un index.

la morale de l'histoire :
bien utiliser les champs indexés pour les jointures !

résolu