Have you considered adding a secondary index on the units column for your delete queries?
DELETE FROM clientinfo WHERE units='155618918'; -----Original Message----- From: Alexandre Vieira [mailto:nul...@gmail.com] Sent: Thursday, September 02, 2010 8:46 AM To: John Daisley; joh...@pixelated.net Cc: mysql@lists.mysql.com Subject: Performance problems on MySQL John, Johnny, Thanks for the prompt answer. mysql> SHOW CREATE TABLE clientinfo; +------------+-------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ----------------------------------------------------------------+ | Table | Create Table | +------------+-------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ----------------------------------------------------------------+ | clientinfo | CREATE TABLE `clientinfo` ( `userid` varchar(21) NOT NULL default '', `units` float default NULL, `date_last_query` datetime default NULL, `last_acc` int(10) unsigned default NULL, `date_last_units` datetime default NULL, `notification` int(10) unsigned NOT NULL default '0', `package` char(1) default NULL, `user_type` varchar(5) default NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +------------+-------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW INDEX FROM clientinfo; +------------+------------+----------+--------------+-------------+--------- --+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------+------------+----------+--------------+-------------+--------- --+-------------+----------+--------+------+------------+---------+ | clientinfo | 0 | PRIMARY | 1 | userid | A | 460056 | NULL | NULL | | BTREE | | +------------+------------+----------+--------------+-------------+--------- --+-------------+----------+--------+------+------------+---------+ 1 row in set (0.00 sec) SELECT * FROM clientinfo WHERE userid='182106617'; UPDATE clientinfo SET units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units =now(),notification=0 WHERE userid='152633876'; INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(), notification=0, package='D', user_type='PRE'; DELETE FROM clientinfo WHERE units='155618918'; There are no other type of queries. We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex On Thu, Sep 2, 2010 at 1:52 PM, John Daisley <daisleyj...@googlemail.com>wrote: > What is the hardware spec? Anything else running on the box? > > Why are you replicating but not making use of the slave? > > Can you post the output of SHOW CREATE TABLE? > > Regards > John > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org