1

When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go. If the table is MyIsam, it works ok. If executed [edited] the very first time a few seconds later (5-10s) it executes in 15 ms, no cache involved. Anyone can explain this behaviour ?

DROP TABLE IF EXISTS tmp_vlanendpoints;
CREATE TABLE tmp_vlanendpoints (
  routeId INTEGER,
  INDEX routeId (routeId)
)
SELECT 
  routeId,
  HostNameOrIp,
  CONCAT(
      HostNameOrIp,
      '»ifIndex=',
      IFNULL(subIfIndex,ifIndex)
  ) AS port_sourcerefcode,
  nodeType
FROM tmp_getdatamultipointtrails
WHERE nodeType='PE' 
    OR (numEDDs=1 AND nodeType='EDD')
    OR (numEDDs=0 AND nodeType='AGG' AND linkId IS NULL);

# Failing SELECT when executed in sequence. Takes 15 ms if executed alone.
SELECT 
    veacpe.HostNameOrIp AS cpe_a,
    veacpe.port_sourcerefcode AS port_acpe,
    veape.HostNameOrIp AS pe_a,
    veape.port_sourcerefcode AS port_ape,
    vezcpe.HostNameOrIp AS cpe_z,
    vezcpe.port_sourcerefcode AS port_zcpe,
    vezpe.HostNameOrIp AS pe_z,
    vezpe.port_sourcerefcode AS port_zpe,
    evc.*
FROM tmp_evcdata evc
JOIN tmp_vlanendpoints veacpe ON veacpe.routeId=evc.routeId_a AND veacpe.nodeType<>'PE'
JOIN tmp_vlanendpoints veape ON veape.routeId=evc.routeId_a AND veape.nodeType='PE'
JOIN tmp_vlanendpoints vezcpe ON vezcpe.routeId=evc.routeId_z AND vezcpe.nodeType<>'PE'
JOIN tmp_vlanendpoints vezpe ON vezpe.routeId=evc.routeId_z AND vezpe.nodeType='PE';

SHOW CREATE TABLE:

CREATE TABLE `tmp_getdatamultipointtrails` (
   `routeId` int(11) DEFAULT NULL,
   `linkId` int(11) DEFAULT '0',
   `deviceId` int(11) DEFAULT NULL,
   `nodeType` varchar(10) DEFAULT NULL,
   `numEDDs` decimal(23,0) DEFAULT NULL,
   `numLooseEnds` decimal(23,0) DEFAULT NULL,
   `leafNE` text,
   `numNEs` bigint(21) NOT NULL DEFAULT '0',
   `missingTopology` text,
   `vlanId` int(11) DEFAULT NULL,
   `CIR` bigint(20) NOT NULL DEFAULT '0',
   `SourceRefCode` text,
   `HostNameOrIp` varchar(255) NOT NULL,
   `serverTrailSourceRefCode` text,
   `entPhysicalIndex` int(10) unsigned DEFAULT NULL,
   `ifIndex` int(10) unsigned DEFAULT NULL,
   `subIfIndex` int(10) unsigned DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

count(*): 19733
select * from tmp_getdatamultipointtrails limit 2;

17 1340    821 PE  1   0   [3122]  3   ''  926 9984    PE821»if284»vlan926 89.13.237.13    10.25.153.4»ifIndex=6»to:89.13.237.133»ifIndex=284  17105425    284 576
17  4701    3122    EDD 1   0   [3122]  3   '' 926  9984    PE821»if284»vlan926    10.250.153.3 10.25.153.3»ifIndex=17»to:10.25.153.3»ifIndex=101720064     7001    101720064   NULL
  • " If executed a few seconds later (5-10s) it executes in 15 ms" - very likely because intermediate query results were still sitting in cache when you ran the query the second time. – Victor Di Leo Aug 28 '18 at 14:27
  • Please post SHOW CREATE TABLE tmp_getdatamultipointtrails and select count(*) from tmp_getdatamultipointtrails and select * from tmp_getdatamultipointtrails limit 2. – Ivanov Sep 4 '18 at 9:10
  • @VictorDiLeo it's not possible to have such a magic cache in MySQL – Ivanov Sep 4 '18 at 9:13
  • 1
    I should not have used "intermediate" in my comment, but query cache is certainly available in MySQL: 1) dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html 2) percona.com/blog/2015/01/02/… 3) Googling "mysql query runs faster second time cache" will show this is a common question. – Victor Di Leo Sep 4 '18 at 14:40
  • As edited, no cache is involved. We have to wait 5-10s that the initial create table finished something, otherwise it stalls completely. – Jean-Pierre B Sep 5 '18 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.