Re: Query slow on Windows, fast on Ubuntu
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;