List:General Discussion« Previous MessageNext Message »
From:Antonio Fernández Pérez Date:June 25 2014 1:24pm
Subject:Problem with INSERT INTO and UPDATE queries
View as plain text  
​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.​

Thread
Problem with INSERT INTO and UPDATE queriesAntonio Fernández Pérez25 Jun 2014