Optimisation d'une requête [Résolu]

[Webselect]

Bonjour,
J'ai du reprendre les travaux d'un programmeur ingénieur en informatique. Donc la structure mal concu de la base de donnée était déjà en place et je ne peux pas la modifier à part ajouter des tables et des index.

Cette base de donnée sert à un odorateur incluant un gestion des projets.

Donc il y a une table repository qui contient les Punch-in et Punch-Out. Par contre l'ingénieur à décidé de créer un enregistrement pour un punch-in et un autre pour un punch-out. Donc pour calculer le temps il faut trouver le punch-out associée a ce punch-in.
Le punch-out peut etre place avant le punch-in dans la table.
Le punch-in peut ne pas avoir de punch-out pour le moment.
Dans certains cas(car l'administrateur peut ajouté/suprimmer manuelement des punch-in/out) il peut avoir 2 punch-in/out de suite mais dans ce temps c'est normal que GIGO s'applique.



Voici la table Repository

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
CREATE TABLE repository (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
username varchar(25) character SET latin1 NOT NULL DEFAULT '',
state tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
date_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
FK_Projet int(11) DEFAULT NULL,
task_id int(11) NOT NULL,
curSalaryCost float(10,2) DEFAULT '0.00',
UNIQUE KEY id (id),
KEY idxUserName (username,FK_Projet,task_id),
KEY idxDateTimeUser (date_time,username)) 
ENGINE=MyISAM  
DEFAULT CHARSET=utf8;

Pour associer les punch in et punch out j'ai créer un VIEW mais elle est lente(2 seconde pour 1800 enregistrements présentement) car je ne suis pas capable d'utilisé WITH dans mysql et je n'ai pas trouver de moyen de faire un jointure effiace donc j'ai utilisé un select imbriqué(tres mauvais). Ca fonctionne mais c'est trop lent.

Voici ma VIEW

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT 
`a`.`id` AS `id`,
`a`.`username` AS `username`,
`a`.`state` AS `state`,
`a`.`date_time` AS `date_time`,
`a`.`FK_Projet` AS `FK_Projet`,
`a`.`task_id` AS `task_id`,
`a`.`curSalaryCost` AS `curSalaryCost`,
`seconds_between`(`a`.`date_time`,ifnull(
                        (SELECT `b`.`date_time`  
                         FROM `repository` `b` 
                         WHERE ((`a`.`username` = `b`.`username`) 
                         AND (`b`.`state` = 2) 
                         AND (`b`.`date_time` > `a`.`date_time`)) ORDER BY `b`.`date_time` LIMIT 0,1),now())) AS `iNbSeconde` 
FROM `repository` `a`
WHERE (`a`.`state` = 1) 
ORDER BY `a`.`date_time`

seconds_between est tout simplement une fonction que j'ai fait pour calculer le nombre de seconde entre 2 date_time.


Donc si vous avez des piste pour m'aider, ca serait grandement apprécié.

Merci à l'avance

[SQLpro]

Commencez par placer les bons index (state, date_time)
Ensuite MySQL n'ayant pas de fonction optimisées comme ROW_NUMBER, LEAD et LAG, le fait de retrouver la ligne suivante ou précédente sera congénitalement lent.
Seule solution passer à un vrai SGBDR vraiment libre comme PostGreSQL qui sait faire ce genre de choses sans problème !
A lire : http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/
http://blog.developpez.com/sqlpro/p9...lles-en-sql-1/

A +

[Webselect]

hehe, merci beaucoup. J'espérais avoir une réponse de toi.

J'ai pas le pouvoir de changer la BD donc j'ai résolu le problème en le contournant.

Je crée tout simplement une table temporaire(qui contient les bons indexs) au début de mon code dans lequel j'insère les données de ma VIEW déjà pré-filtrer.

Par la suite j'interroge cette table temporaire qui est vraiment plus rapide. Et avant de finir mon code, j'efface cette table.

C'est sûr que j'aurais pus aussi créer cette table permanente et l'incrémenter avec des triggers mais je n'ai même pas accès a ceux-ci non plus


En passant SQLpro, j'ai appris 90% de ce que je sais des Bases de Données par tes tutoriels et faut croire que c'est vraiment mieux que ce qu'ils enseignent dans les universités du Québec.

[SQLpro]

Oui, sauf que apprendre avec MySQL c'est quand même plus que limite.... Ce n'est toujours pas un vrai SGBD relationnel !

A +

[Webselect]

Envoyé par SQLpro

Oui, sauf que apprendre avec MySQL c'est quand même plus que limite.... Ce n'est toujours pas un vrai SGBD relationnel !

A +

t'inquiete, J'ai apris avec MS SQL et son Transact SQL