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.
