| Prasanna Raj 2006-04-04, 7:33 am |
| 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=E1nchez Mora <Arsanchez@bp.fi.cr> wrote:
> This table holds latest data from an app:
> =20
> mysql> select * from ultimas_respuestas_s
nmp limit 10;
> +----+----------------+-----------------------+-------------------------+=
-----------+------------+----------+
> | id | info_oficina | columna_donde_guarda
r | 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)
> =20
> without the "limit 10"
> =20
> 1780 rows in set (0.03 sec)
> =20
> the create table:
> =20
> ultimas_respuestas_s
nmp CREATE TABLE `ultimas_respuestas_
snmp` ( =
=20
> `id` int(4) NOT NULL default '0', =
=20
> `info_oficina` varchar(35) default NULL, =
=20
> `columna_donde_guard
ar` varchar(30) default NU=
LL, =20
> `info_interfaz` varchar(30) default NULL, =
=20
> `valorSNMP` varchar(12) default NULL, =
=20
> `nombre_dns` varchar(20) default NULL, =
=20
> `hora` varchar(10) default NULL =
=20
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1=20
> =20
> I cannot use indexes because my dbexpress driver doesn't support it (long=
story short, I'll change it in the next version).
> =20
> Now, I have to make a lot of sequential updates, like
> =20
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"1", hora =3D"18:47:21" W=
HERE id =3D"0";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"10", hora =3D"18:47:22" =
WHERE id =3D"1";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"1", hora =3D"18:47:22" W=
HERE id =3D"2";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"1", hora =3D"18:47:22" W=
HERE id =3D"3";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"1", hora =3D"18:47:23" W=
HERE id =3D"4";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"1", hora =3D"18:47:23" W=
HERE id =3D"5";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"5", hora =3D"18:47:24" W=
HERE id =3D"6";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"5", hora =3D"18:47:24" W=
HERE id =3D"7";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"7000", hora =3D"18:47:24=
" WHERE id =3D"8";
> .
> .
> .
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"0", hora =3D"18:48:38" W=
HERE id =3D"1778";
> UPDATE ultimas_respuestas_S
NMP SET valorSNMP =3D"", hora =3D"18:48:38" WH=
ERE id =3D"1779";
> =20
> This makes my server CPU load top 100% for about 1:20 s.=20
> =20
> First question: is update the best command for this? I've seen replace th=
at might work too; has anyone played around with something like this before?
> =20
> Second: is there a better way of formulating the update command, for this=
sequence?? Perhaps one that takes advantage of the sequential inserts?
> =20
> TIA, all comments welcome. I am a newbie by the way, trying to optimize m=
y first MySQL related program.
> =20
> Ariel
>=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|