Play with triggers when you need to insert indexes on synced tables

[yb]

Hi,

I need to create indexes on synced tables to improve performance on
mysql clients.

I did it this way :


On the synced table i launch a script that :

1) Save triggers from table to buffer

2) Drop triggers

3) Add needed indexes

4) Recreate triggers from buffer

<?php
include('connect.php');
//Listing current triggers
$sql = "SHOW TRIGGERS WHERE `Table` = 'TIERS'";
$trigger_rows_t = mysql_query($sql);
$trigger_rows = array();

//Disable current triggers
?>
<label>Suppression des triggers</label>
<ul>
<?php
while ($row = mysql_fetch_array($trigger_rows_t, MYSQL_ASSOC)) {
$trigger_rows[] = $row;
echo '<li> TRIGGER : '.$row['Trigger'].' : '.$sql.'</li>';
$sql = "DROP TRIGGER IF EXISTS `{$row['Trigger']}`";
mysql_query($sql)or die(mysql_error());
}
?>
</ul>
<?php
mysql_free_result($trigger_rows_t);

/*-----------------------------------------------------------
JOB ADD INDEXES
------------------------------------------------------------*/
//ADD KEY `IDARBO1` (`IDARBO1`),


$myreq = "ALTER TABLE TIERS
ADD KEY `IDX_REFTIERS` (`REFTIERS`),
ADD KEY `STATUT` (`STATUT`),
ADD KEY `ISFOURNISSEUR` (`ISFOURNISSEUR`)
";
mysql_query($myreq)or die(mysql_error());

echo '<hr><br/><label>Ajout des index</label><br/>'.$myreq.'<hr>';

?>
<label>Re-cr&eacute;ation des triggers</label>
<ul>
<?php
//Re-create triggers
foreach ($trigger_rows as $item )
{

$sql= "CREATE TRIGGER `{$item['Trigger']}` {$item['Timing']}
{$item['Event']} ON `{$item['Table']}`
FOR EACH ROW {$item['Statement']}";

mysql_query($sql)or die(mysql_error());
}
?>





What is the incidence of these commands ? Could it create problems ?

Thanks.

[pervasync]

Hi Yann,

Can you just create the indexes without dropping and re-creating the
triggers?

Thanks,
Pervasync Support