MySQL Forums
Forum List  »  Performance

Re: Query slow on Windows, fast on Ubuntu
Posted by: Thomas Thomas
Date: January 23, 2018 03:13AM

Hi,
The query is :
select
client1_.nom as col_0_0_,
vente0_.client_id as col_1_0_,
vente0_.date_livraison as col_2_0_,
vente0_.noBL as col_3_0_,
vente0_.id_facture as col_4_0_,
vente0_.puht as col_5_0_,
vente0_.avoir as col_6_0_,
vente0_.id_facture_avoir as col_7_0_,
vente0_.nb_factures_imprimees as col_8_0_,
sum(vente0_.montant_ht) as col_9_0_,
vente0_.id_avoir as col_10_0_,
client1_.facturation_edi as col_11_0_
from
T_VENTE vente0_,
T_CLIENT client1_
where
vente0_.client_id = client1_.id
group by client1_.nom , vente0_.client_id , vente0_.date_livraison , vente0_.noBL , vente0_.avoir , vente0_.export_ciel , vente0_.avoir_valide
having vente0_.date_livraison <= '2018-01-10'
and vente0_.avoir = 0
and (vente0_.noBL is not null)
and vente0_.noBL <> ''
and (vente0_.id_facture = ''
or vente0_.id_facture is null)
and vente0_.export_ciel = 0

#############################################################################
the explain output:
#############################################################################
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,SIMPLE,client1_,ALL,PRIMARY,NULL,NULL,NULL,709,"Using temporary; Using filesort"
1,SIMPLE,vente0_,ref,"FK_jamambbhks5qiqakouo189hjk,index11",index11,9,myapp.client1_.id,266,"Using where"

#############################################################################
table definitions :
client :
#############################################################################
CREATE TABLE `T_CLIENT` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`banque` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`bic` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`bon_livraison_defaut` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`code` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`code_comptable` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`commentaire_facture` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`compte_contrepartie` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`compte_ristourne_provision` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`creance_client_par_affacturage` bit(1) DEFAULT NULL,
`delai_livraison` int(11) DEFAULT NULL,
`encours_actuel_client` decimal(10,2) DEFAULT NULL,
`facturation_edi` bit(1) DEFAULT NULL,
`groupement_facture_par_ref_client` bit(1) DEFAULT NULL,
`iban` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`mail_envoi_facture` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`mention_facture` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`montant_agrement_accorde` decimal(10,2) DEFAULT NULL,
`no_dossier_assurance_credit` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`no_tva_intracommunautaire` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`nom` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`plafond_autorise` decimal(10,2) DEFAULT NULL,
`remise_traite_magnetique` bit(1) DEFAULT NULL,
`rib` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`siret` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`soumis_ctifl` bit(1) DEFAULT NULL,
`soumis_interfel` bit(1) DEFAULT NULL,
`soumis_tva` bit(1) DEFAULT NULL,
`taux_provision_ristourne` decimal(10,4) DEFAULT NULL,
`taux_remise_sur_facture` decimal(10,4) DEFAULT NULL,
`langue_id` bigint(20) DEFAULT NULL,
`reglement_id` bigint(20) DEFAULT NULL,
`ristourne_id` bigint(20) DEFAULT NULL,
`typeClient_id` bigint(20) DEFAULT NULL,
`vendeur_id` bigint(20) DEFAULT NULL,
`code_ristourne` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`code_vendeur` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`email_bl` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`activated` bit(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_14p9n5na46r6441c704mhk83x` (`reglement_id`),
KEY `FK_3asdsnx9210rewo05mbs7nxve` (`langue_id`),
KEY `FK_lg6letfjfjjegdrou0h34c0dq` (`typeClient_id`),
KEY `FK_m602rtq8igonhm432mnyhtqer` (`vendeur_id`),
KEY `FK_qi3a0gq92bmvmt7s09av4pgae` (`ristourne_id`),
CONSTRAINT `FK_14p9n5na46r6441c704mhk83x` FOREIGN KEY (`reglement_id`) REFERENCES `T_REGLEMENT` (`id`),
CONSTRAINT `FK_3asdsnx9210rewo05mbs7nxve` FOREIGN KEY (`langue_id`) REFERENCES `T_LANGUE` (`id`),
CONSTRAINT `FK_lg6letfjfjjegdrou0h34c0dq` FOREIGN KEY (`typeClient_id`) REFERENCES `T_TYPECLIENT` (`id`),
CONSTRAINT `FK_m602rtq8igonhm432mnyhtqer` FOREIGN KEY (`vendeur_id`) REFERENCES `T_VENDEUR` (`id`),
CONSTRAINT `FK_qi3a0gq92bmvmt7s09av4pgae` FOREIGN KEY (`ristourne_id`) REFERENCES `T_RISTOURNE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=643 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


############################################################################
vente :
############################################################################
CREATE TABLE `T_VENTE` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`qte_commandee` int(11) DEFAULT NULL,
`qte_livree` int(11) DEFAULT NULL,
`tare` decimal(10,2) DEFAULT NULL,
`poids_net` decimal(10,2) DEFAULT NULL,
`poids_livre` decimal(10,2) DEFAULT NULL,
`valorisation` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`pieces` int(11) DEFAULT NULL,
`puht` decimal(10,3) DEFAULT NULL,
`montant_ht` decimal(10,2) DEFAULT NULL COMMENT 'Montant avec la remise déduite',
`montant_tva` decimal(10,2) DEFAULT NULL,
`taux_tva` decimal(10,3) DEFAULT NULL,
`lot_id` bigint(20) DEFAULT NULL,
`stock_id` bigint(20) DEFAULT NULL,
`num_commande` int(11) DEFAULT NULL,
`num_commande_detail` int(11) DEFAULT NULL,
`facturee` bit(1) DEFAULT NULL,
`livraisonId` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`client_id` bigint(20) DEFAULT NULL,
`date_livraison` date DEFAULT NULL,
`calibre` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`categorie` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`refClient` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`certificatFournisseur` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`clientAdresse_id` bigint(20) DEFAULT NULL,
`marque` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`deuxiemeClientAdresse_id` bigint(20) DEFAULT NULL,
`transport_id` bigint(20) DEFAULT NULL,
`noBL` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`avoir` bit(1) DEFAULT NULL,
`id_facture` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`id_facture_avoir` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`montant_remise` decimal(10,2) DEFAULT NULL,
`montant_ristourne` decimal(10,2) DEFAULT NULL,
`vendeur_id` bigint(20) DEFAULT NULL,
`commentaire` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`echeance` date DEFAULT NULL,
`montant_ristourne_tva` decimal(10,2) DEFAULT NULL,
`palette_chep` bit(1) DEFAULT NULL,
`reglee` bit(1) DEFAULT NULL,
`type_avoir_colis` bit(1) DEFAULT NULL,
`type_avoir_montant` bit(1) DEFAULT NULL,
`type_avoir_poids` bit(1) DEFAULT NULL,
`type_chep` bit(1) DEFAULT NULL,
`date_facture` date DEFAULT NULL,
`facture` bit(1) DEFAULT NULL,
`client_code` varchar(45) COLLATE utf8_bin DEFAULT NULL,
`transport_code` varchar(45) COLLATE utf8_bin DEFAULT NULL,
`vendeur_code` varchar(45) COLLATE utf8_bin DEFAULT NULL,
`type_avoir_piece` bit(1) DEFAULT NULL,
`id_avoir` bigint(20) DEFAULT NULL,
`avoir_valide` bit(1) DEFAULT NULL,
`nb_colis_retour` int(11) DEFAULT NULL,
`bl_envoye` bit(1) DEFAULT NULL,
`montant_ctifl` decimal(10,2) DEFAULT NULL,
`montant_interfel` decimal(10,2) DEFAULT NULL,
`montant_tva_ctifl` decimal(10,2) DEFAULT NULL,
`montant_tva_interfel` decimal(10,2) DEFAULT NULL,
`date_echeance` date DEFAULT NULL,
`nb_factures_imprimees` int(11) DEFAULT NULL,
`montant_avoir_force` bit(1) DEFAULT NULL,
`facture_envoyee_mail` bit(1) DEFAULT NULL,
`date_envoiBl` varchar(45) COLLATE utf8_bin DEFAULT NULL,
`heure_envoiBl` varchar(45) COLLATE utf8_bin DEFAULT NULL,
`export_ciel` bit(1) DEFAULT NULL,
`aImprimerBL` bit(1) DEFAULT NULL,
`num_commande_client` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`avoir_bl_referenced` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`pieces_avoir` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_vente_lot_id` (`lot_id`),
KEY `fk_vente_stock_id` (`stock_id`),
KEY `FK_jamambbhks5qiqakouo189hjk` (`client_id`),
KEY `FK_2inwe1jsnj8jxnlw1l7jdaihi` (`clientAdresse_id`),
KEY `FK_jlln0sgnuqdpbyutfp2t1afgw` (`deuxiemeClientAdresse_id`),
KEY `FK_tpyxpld1yju5miqu42hntacc8` (`transport_id`),
KEY `FK_lvk7kc075vaxwhu0uaj7dgyb0` (`vendeur_id`),
KEY `index11` (`client_id`),
KEY `index12` (`noBL`),
KEY `index13` (`id_facture`),
KEY `index14` (`num_commande_detail`),
KEY `index_commande` (`num_commande`),
CONSTRAINT `FK_2inwe1jsnj8jxnlw1l7jdaihi` FOREIGN KEY (`clientAdresse_id`) REFERENCES `T_ADRESSE` (`id`),
CONSTRAINT `FK_jamambbhks5qiqakouo189hjk` FOREIGN KEY (`client_id`) REFERENCES `T_CLIENT` (`id`),
CONSTRAINT `FK_jlln0sgnuqdpbyutfp2t1afgw` FOREIGN KEY (`deuxiemeClientAdresse_id`) REFERENCES `T_ADRESSE` (`id`),
CONSTRAINT `FK_lvk7kc075vaxwhu0uaj7dgyb0` FOREIGN KEY (`vendeur_id`) REFERENCES `T_VENDEUR` (`id`),
CONSTRAINT `FK_tpyxpld1yju5miqu42hntacc8` FOREIGN KEY (`transport_id`) REFERENCES `T_FOURNISSEUR` (`id`),
CONSTRAINT `fk_vente_lot_id` FOREIGN KEY (`lot_id`) REFERENCES `T_LOT` (`id`),
CONSTRAINT `fk_vente_stock_id` FOREIGN KEY (`stock_id`) REFERENCES `T_STOCK` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=351495 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Options: ReplyQuote


Subject
Views
Written By
Posted
4574
January 22, 2018 09:24AM
Re: Query slow on Windows, fast on Ubuntu
1778
January 23, 2018 03:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.