Optimisation requête (Mysql)

Recherche :

Mot : Pseudo : Filtrer
Bas de page
Auteur	Sujet : Optimisation requête (Mysql)

PunkRod

Digital Mohawk

Hello,

J'ai la requête suivante qui mets un temps qui me parait invraisemblable (une vingtaine de secondes à être traitée) pour quelques milliers d'enregistrements à parcourir.
Je travaille sur un serveur Mysql 5.

Le but de la requête est d'extraire des documents dans un état particulier, et qui ne doivent pas voir de documents rattachés
(concrètement des factures sans avoirs)

SELECT `id_doc`
FROM `docs`
WHERE `id_type` = '4'
AND `etat` != '7'
AND `id_doc` NOT IN ( SELECT `id_doc_pere`
FROM `docs`
WHERE `id_type` = '5')
ORDER BY `date_echeance` DESC;

Je n'utilise pas régulièrement les requêtes imbriquées, et vu le temps de traitement de la requête, je me demande si je ne devrais pas faire 2 requetes et effectuer mon filtrage via mon langage de programmation.

Est-ce que je m'y prends comme un manche ? ou suis-je limité par autre chose ?

Message édité par PunkRod le 27-09-2007 à 15:10:58

Publicité

anapajari

s/travail/glanding on hfr/gs;

Le plus "simple" avec un not exits

Code :

SELECT d1.`id_doc`  
    FROM `docs` d1 
    WHERE d1.`id_type` = '4'  
    AND d1.`etat` != '7'
    AND NOT exits ( SELECT d2.`id_doc_pere`
                               FROM `docs`d2
                               WHERE d2.`id_type` = '5' AND d1.`id_doc` =d2.`id_doc_pere`)
    ORDER BY d1.`date_echeance` DESC;

La même avec un left outer join + condition à null

Code :

SELECT d1.`id_doc`
FROM `docs` d1
LEFT OUTER JOIN `docs` d2 on d2.id_doc_pere = d1.id_doc and d2.id_type='5'
WHERE d1.`id_type` = '4'
AND d1.`etat` != '7'
AND d2.id_doc_pere is NULL
ORDER BY d1.`date_echeance` DESC;

Message édité par anapajari le 27-09-2007 à 16:20:42

PunkRod

Digital Mohawk

Merci pour l'astuce, je teste ça dans un instant !!! :jap:

edit : ça marche, mais le traitement reste un peu long

Message édité par PunkRod le 27-09-2007 à 18:30:08

couak

crée un ou pluisuers index

PunkRod

Digital Mohawk

J'ai créé l'index sur le type de document (id_type) et là les effets sont instantanés, je suis tombé à 3 secondes pour la requête, ce qui est largement plus acceptable !
Je repenserais à indexer mes valeurs, merci ! :jap:

zapan666

Tout est relatif

pour voir ce que fais ta requete, tu peux aussi utiliser EXPLAIN

Code :

EXPLAIN SELECT * FROM bidule;

---------------
my flick r - Just Tab it !

MagicBuzz

+1 pour les index, parceque là, tu pourras pas faire mieux en terme de requête (la seconde proposé par anajapari étant la meilleure)

Message cité 1 fois

MagicBuzz

moidifie ton index que t'as créé sur "id_type" et rajoute "etat" dans l'index.

anapajari

s/travail/glanding on hfr/gs;

MagicBuzz a écrit :

+1 pour les index, parceque là, tu pourras pas faire mieux en terme de requête (la seconde proposé par anajapari étant la meilleure)

en toute théorie elles sont equivalentes puisque l'optimizer est censé convertir la 1ere dans quelque chose de très proche de la deuxième.
Maintenant ça reste mysql ( qui reserve ces surprises) donc je garantis pas que ça soit effectivement le cas

PunkRod

Digital Mohawk

J'aii testé avec Explain justement.
La requête avec le LEFT OUTER JOIN fait au final 2 query de type 'SIMPLE' (c'est l'intitulé de la colonne qui m'est renvoyé ), là où la requête imbriquée fait une requête 'PRIMARY' et une 'DEPENDENT SUBQUERY'. le left outer join fait gagner quelques ms (bon forcément maintenant que c'est indexé j'ai du mal à bien évaluer les écarts de performances).

J'essaie de trouver quelques infos sur ces types de requêtes, pour voir les enjeux en la matière. Préférer des requêtes 'simple' ou pas...

Publicité

Mosca

Hello,

Je me permets un petit up pour rester dans le sujet de l'optimisation.
J'ai cette requête qui me pose des soucis :

Code :

select a.article, a.titre, a.descri, b.sortie, b.prix, b.flag from articles a, groupes b
where a.article = b.article
and b.pays = '01'
and a.article like '001%' or a.article like 'Z%'

Explain :

Code :

+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | a | range | PRIMARY | PRIMARY | 15 | NULL | 2093 | Using where |
| 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 138377 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+

Clé primaire en table a sur article et en table b sur article + pays

Le select n'en finit pas de tourner et se termine par un "killed"

Une idée ?

Message édité par Mosca le 16-11-2007 à 11:02:58

PunkRod

Digital Mohawk

j'suis jamais certain pour tout ce qui est booléen, donc je mets des parenthèses moi même quand je mixe les AND et OR :
qu'est ce que ça donne si tu modifies ta requête comme ça :

Code :

select a.article, a.titre, a.descri, b.sortie, b.prix, b.flag from articles a, groupes b
where a.article = b.article
and b.pays = '01'
and (a.article like '001%' or a.article like 'Z%')

Message cité 1 fois

anapajari

s/travail/glanding on hfr/gs;

profites-en pour ecrire ta jointure explicitement ( et au passage je l'inverserais):

Code :

SELECT 
 a.article, a.titre, a.descri, b.sortie, b.prix, b.flag
FROM
 groupes b
 INNER JOIN articles a ON a.article = b.article 
WHERE
 b.payes = '01'
AND AND (b.article LIKE '001%' OR b.article LIKE 'Z%')

comme ça toutes tes conditions sont sur la même table.

Par ailleurs si ton champs article est un très grand varchar, tu gagnerais surement s'pas terrible d'utiliser du like %

Message édité par anapajari le 16-11-2007 à 11:38:42

Mosca

PunkRod a écrit :

j'suis jamais certain pour tout ce qui est booléen, donc je mets des parenthèses moi même quand je mixe les AND et OR :
qu'est ce que ça donne si tu modifies ta requête comme ça :

Code :

select a.article, a.titre, a.descri, b.sortie, b.prix, b.flag from articles a, groupes b
where a.article = b.article
and b.pays = '01'
and (a.article like '001%' or a.article like 'Z%')

Ca change tout (0.01 sec) !

Merci :jap:

C'est la jointure qui semble poser problème, car le même type de requête sur la seule table articles est aussi rapide

Mosca

@anapajari : merci pour le conseil, c'est vrai que n'ai pas l'habitude d'écrire les jointures de cette façon, par souci de rapidité ...

:jap:

Quand au like '%' : c'est un varchar(13), et l'utilisation du LIKE est assez aisée (voire obligée) avec ce champ ...

MagicBuzz

Mosca > C'est pas trop le problème de la jointure le souci (normalement l'optimiseur se débrouille), mais ton "or". Un "or" doit toujours être entre parenthèses, sinon il s'applique quelque soient les "and" autour :

Code :

SELECT *
FROM personne
WHERE active = 'Y' AND age = 20 OR sexe = 'M'

Sélectionne "toutes les personnes actives agées de 20 ans" ET "toutes les personnes de sexe masculin, quelque soit leur age et qu'elles soient actives ou non".
=> En gros ton OR va te faire retourner des millions d'infos. Vu qu'en plus le tiens était sur un "like", ça empêchait l'utilsation des index sur "pays"... Autant dire tu foutais ton SGBD à genoux pour un résultat foireux

FORUM HardWare.fr

Programmation

SQL/NoSQL

Optimisation requête (Mysql)

Sujets relatifs
[RESOLU] - Probleme requete SQL - RETURN	Aide sur requete SQL avec variable PHP
MySQL - Optimisation d'une requête avec plusieurs JOIN	Optimisation d'une requete mysql
[MySQL] Besoin d'aide - Optimisation d'une requête très lourde	[MySQL] Optimisation requete sur ENORME table ...
[PHP - MYSQL] optimisation d'une requete	[mysql]optimisation d'un requete
[MySQL]optimisation requete	[MYSQL PHP] Requete ne renvoyant qu'1 champ, optimisation du code.
Plus de sujets relatifs à : Optimisation requête (Mysql)

Page générée en 0.071 secondes