Try creating INDEX on id column I think that will do the trick ;)
--Praj On Mon, 3 Apr 2006 19:07:34 -0600 Ariel Sánchez Mora <[EMAIL PROTECTED]> wrote: > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
