0

Simple update is very slow and causing high load average with large amount of server :

table name :

CREATE TABLE IF NOT EXISTS `LISTE_PROMOTION3` (
  `id_list_prom` int(11) NOT NULL AUTO_INCREMENT,
  `id_hotel` int(11) NOT NULL,
  `id_marche` int(11) NOT NULL,
  `lib_promo` varchar(250) NOT NULL,
  `etat_promo` enum('T','F') NOT NULL DEFAULT 'T',
  `type_promo` varchar(250) NOT NULL,
  `debut_vente` date NOT NULL,
  `fin_vente` date NOT NULL,
  `debut_sejour` date NOT NULL,
  `fin_sejour` date NOT NULL,
  `min_sty` int(11) NOT NULL,
  `max_sty` int(11) NOT NULL,
  `free_night` int(11) NOT NULL,
  `type_free_night` varchar(250) NOT NULL,
  `priorite_promo` int(11) NOT NULL,
  `typ_prom` varchar(250) NOT NULL,
  `pourcent_red_lpd` float NOT NULL,
  `pourcent_red_dp` float NOT NULL,
  `pourcent_red_dpplus` float NOT NULL,
  `pourcent_red_pc` float NOT NULL,
  `pourcent_red_pcplus` float NOT NULL,
  `pourcent_red_aisoft` float NOT NULL,
  `pourcent_red_allin` float NOT NULL,
  `pourcent_red_ultraai` float NOT NULL,
  `montant_red_lpd` float NOT NULL,
  `montant_red_dp` float NOT NULL,
  `montant_red_dpplus` float NOT NULL,
  `montant_red_pc` float NOT NULL,
  `montant_red_pcplus` float NOT NULL,
  `montant_red_aisoft` float NOT NULL,
  `montant_red_allin` float NOT NULL,
  `montant_red_ultraai` float NOT NULL,
  `id_agent` int(11) NOT NULL,
  `date_insertion` datetime NOT NULL,
  `date_modif` datetime NOT NULL,
  `id_agent_modif` int(11) NOT NULL,
  `typ_app_prom` varchar(250) NOT NULL,
  `sur_tarif` varchar(250) NOT NULL,
  `pourcent_red_ls` float NOT NULL,
  `montant_red_ls` float NOT NULL,
  PRIMARY KEY (`id_list_prom`),
  KEY `id_hotel` (`id_hotel`),
  KEY `etat_promo` (`etat_promo`),
  KEY `fin_vente` (`fin_vente`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9480 ;

The table contains 9480 rows.

Query:

UPDATE `LISTE_PROMOTION` SET `etat_promo` = 'T' WHERE `id_list_prom` = 9460;

This query update 1 row and takes around 25 seconds to complete and causing high load average with large amount of server.

2 Answers 2

0

Get rid of indexes on flags (etat_promo). The Optimizer almost never uses such; it will prefer to do a table scan. If you have some query that you think might be using that index, run the EXPLAIN and show us the query for further discussion.

0
0

EXPLAIN UPDATE LISTE_PROMOTION SET etat_promo = 'T' WHERE id_list_prom = 9460;

enter image description here

1
  • Has LISTE_PROMOTION3 been renamed to LISTE_PROMOTION? The SHOW CREATE TABLE is for LISTE_PROMOTION3. ANALYZE or OPTIMIZE been run on the table in past 10 days? Wondering why we do not see select_type of EQ_REF instead of SIMPLE? Commented Jun 23, 2018 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.