This table holds latest data from an app:
mysql> select * from ultimas_respuestas_snmp limit 10;
+----+----------------+-----------------------+-------------------------+-----------+------------+----------+
| id | info_oficina | columna_donde_guardar | info_interfaz |
valorSNMP | nombre_dns | hora |
+----+----------------+-----------------------+-------------------------+-----------+------------+----------+
| 0 | Sucursal Canas | USO_CPU_1min | " | error
| canas | 18:49:53 |
| 1 | Sucursal Canas | RAM_LIBRE | " | error
| canas | 18:49:54 |
| 2 | Sucursal Canas | ESTADO_ADMIN_1 | TDM 195-2883 ICE | 1
| canas | 18:49:55 |
| 3 | Sucursal Canas | ESTADO_ADMIN_2 | RDSI 669-9010 ICE | error
| canas | 18:49:56 |
| 4 | Sucursal Canas | ESTADO_ADMIN_3 | RDSI_doble 669-9010 ICE | error
| canas | 18:49:57 |
| 5 | Sucursal Canas | ESTADO_PROTOCOLO_1 | TDM 195-2883 ICE | error
| canas | 18:49:58 |
| 6 | Sucursal Canas | ESTADO_PROTOCOLO_2 | RDSI 669-9010 ICE | error
| canas | 18:49:59 |
| 7 | Sucursal Canas | ESTADO_PROTOCOLO_3 | RDSI_doble 669-9010 ICE | 5
| canas | 18:50:00 |
| 8 | Sucursal Canas | BW_ENTRADA_1 | TDM 195-2883 ICE | error
| canas | 18:50:01 |
| 9 | Sucursal Canas | BW_ENTRADA_2 | RDSI 669-9010 ICE | error
| canas | 18:50:02 |
+----+----------------+-----------------------+-------------------------+-----------+------------+----------+
10 rows in set (0.00 sec)
without the "limit 10"
1780 rows in set (0.03 sec)
the create table:
ultimas_respuestas_snmp CREATE TABLE `ultimas_respuestas_snmp` (
`id` int(4) NOT NULL default '0',
`info_oficina` varchar(35) default NULL,
`columna_donde_guardar` varchar(30) default NULL,
`info_interfaz` varchar(30) default NULL,
`valorSNMP` varchar(12) default NULL,
`nombre_dns` varchar(20) default NULL,
`hora` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I cannot use indexes because my dbexpress driver doesn't support it (long story
short, I'll change it in the next version).
Now, I have to make a lot of sequential updates, like
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:21" WHERE id
="0";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="10", hora ="18:47:22" WHERE id
="1";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:22" WHERE id
="2";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:22" WHERE id
="3";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:23" WHERE id
="4";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="1", hora ="18:47:23" WHERE id
="5";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="5", hora ="18:47:24" WHERE id
="6";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="5", hora ="18:47:24" WHERE id
="7";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="7000", hora ="18:47:24" WHERE id
="8";
.
.
.
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="0", hora ="18:48:38" WHERE id
="1778";
UPDATE ultimas_respuestas_SNMP SET valorSNMP ="", hora ="18:48:38" WHERE id
="1779";
This makes my server CPU load top 100% for about 1:20 s.
First question: is update the best command for this? I've seen replace that
might work too; has anyone played around with something like this before?
Second: is there a better way of formulating the update command, for this
sequence?? Perhaps one that takes advantage of the sequential inserts?
TIA, all comments welcome. I am a newbie by the way, trying to optimize my
first MySQL related program.
Ariel
