Hi list, I have some problems with INSERT INTO and UPDATE queries on a big table. Let me put the code and explain it ...
I have copied the create code of the table. This table has more than 15000000 rows. Create Table: CREATE TABLE `radacct` ( `RadAcctId` bigint(21) NOT NULL AUTO_INCREMENT, `AcctSessionId` varchar(32) NOT NULL DEFAULT '', `AcctUniqueId` varchar(32) NOT NULL DEFAULT '', `UserName` varchar(64) NOT NULL DEFAULT '', `Realm` varchar(64) DEFAULT '', `NASIPAddress` varchar(15) NOT NULL DEFAULT '', `NASPortId` varchar(15) DEFAULT NULL, `NASPortType` varchar(32) DEFAULT NULL, `AcctStartTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `AcctStopTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `AcctSessionTime` int(12) DEFAULT NULL, `AcctAuthentic` varchar(32) DEFAULT NULL, `ConnectInfo_start` varchar(50) DEFAULT NULL, `ConnectInfo_stop` varchar(50) DEFAULT NULL, `AcctInputOctets` bigint(20) DEFAULT NULL, `AcctOutputOctets` bigint(20) DEFAULT NULL, `CalledStationId` varchar(50) NOT NULL DEFAULT '', `CallingStationId` varchar(50) NOT NULL DEFAULT '', `AcctTerminateCause` varchar(32) NOT NULL DEFAULT '', `ServiceType` varchar(32) DEFAULT NULL, `FramedProtocol` varchar(32) DEFAULT NULL, `FramedIPAddress` varchar(15) NOT NULL DEFAULT '', `AcctStartDelay` int(12) DEFAULT NULL, `AcctStopDelay` int(12) DEFAULT NULL, `XAscendSessionSvrKey` varchar(10) DEFAULT NULL, PRIMARY KEY (`RadAcctId`), KEY `user_start` (`UserName`,`AcctStartTime`), KEY `nasip_starttime` (`NASIPAddress`,`AcctStartTime`), KEY `stop_nasip_start` (`AcctStopTime`,`NASIPAddress`,`AcctStartTime`), KEY `acctsesid_user_nasip` (`UserName`,`AcctSessionId`,`NASIPAddress`), KEY `user_stop` (`UserName`,`AcctStopTime`) ) ENGINE=InnoDB AUTO_INCREMENT=17694651 DEFAULT CHARSET=utf8 ############################################################################### The next text shows the entries in mysql-slow.log. ############################################################################### # Time: 140625 9:37:45 # User@Host: radius[radius] @ [192.168.0.30] # Thread_id: 94892163 Schema: radius Last_errno: 0 Killed: 0 # Query_time: 2.327159 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 0 Rows_affected: 1 Rows_read: 0 # Bytes_sent: 19 use radius; SET timestamp=1403681865; INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctau thentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, xascendsessionsvrkey) VALUES ('80004ef0', '78d3fc2661258da5', 'zu629LAYUT', '', '178.136.71.251', '2147503856', 'Wireless-802.11', '2014 -06-25 09:37:26', '0000-00-00 00:00:00', '0', '', '', '', '0', '0', 'tururu', '00-00-11-11-11-11', '', '', '', '178.136.71.1', '0', '0', ''); # User@Host: radius[radius] @ [192.168.0.31] # Thread_id: 97905294 Schema: radius Last_errno: 0 Killed: 0 # Query_time: 2.397604 Lock_time: 0.000062 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 Rows_read: 1 # Bytes_sent: 52 SET timestamp=1403681865; UPDATE radacct SET framedipaddress = '182.138.214.240', acctsessiontime = '4199', acctinputoctets = '0' << 32 | '12327909', acctoutputo ctets = '0' << 32 | '294177486' WHERE acctsessionid = '805063b1' AND username = 'fa239DADUX' AND nasipaddress = '182.138.214.50'; ############################################################################### The previous query is converted because I want to use EXPLAIN ... ############################################################################### SELECT framedipaddress = '172.21.13.152', acctsessiontime = '4199', acctinputoctets = '0' << 32 | '12327909', acctoutputo ctets = '0' << 32 | '294177486' FROM radacct WHERE acctsessionid = '805063b1' AND username = 'fa239DADUX' AND nasipaddress = '192.168.254.10'; +----+-------------+---------+------+-----------------------------------------------------------+----------------------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------------------------------------------------+----------------------+---------+-------------------+------+-------------+ | 1 | SIMPLE | radacct | ref | user_start,nasip_starttime,acctsesid_user_nasip,user_stop | acctsesid_user_nasip | 339 | const,const,const | 1 | Using where | +----+-------------+---------+------+-----------------------------------------------------------+----------------------+---------+-------------------+------+-------------+ 1 row in set (0.10 sec) ############################################################################### Any ideas? I have checked indexes, query's execution time ... All seems work fine ... Thanks in advance. Regards, Antonio.