API tools faq
paste
Advertisement
gedia

Untitled

gedia
Dec 19th, 2018
142
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 16.41 KB | None | 0 0
raw download clone embed print report
  1. QUERY
  2. -----
  3.  
  4. SELECT id,
  5.        date,
  6.        Floor(micro_ts / 1000) AS milli_ts,
  7.        micro_ts,
  8.        method,
  9.        reply_reason,
  10.        ruri,
  11.        ruri_user,
  12.        ruri_domain,
  13.        from_user,
  14.        from_domain,
  15.        from_tag,
  16.        to_user,
  17.        to_domain,
  18.        to_tag,
  19.        pid_user,
  20.        contact_user,
  21.        auth_user,
  22.        callid,
  23.        callid_aleg,
  24.        via_1,
  25.        via_1_branch,
  26.        cseq,
  27.        diversion,
  28.        reason,
  29.        content_type,
  30.        auth,
  31.        user_agent,
  32.        source_ip,
  33.        source_port,
  34.        destination_ip,
  35.        destination_port,
  36.        contact_ip,
  37.        contact_port,
  38.        originator_ip,
  39.        originator_port,
  40.        correlation_id,
  41.        proto,
  42.        family,
  43.        rtp_stat,
  44.        type,
  45.        node,
  46.        custom_field1,
  47.        custom_field2,
  48.        custom_field3,
  49.        msg
  50. FROM   sip_capture_call_20181219
  51. WHERE  ( date BETWEEN From_unixtime(1545231736) AND From_unixtime(1545236093) )
  52.        AND (( callid = '61858b0d6ccd67e60b3a9dfa5ea19b53@voips-ng.modulus.gr'
  53.                OR callid = '459774299@192_168_1_8' ))
  54. ORDER  BY id DESC
  55. LIMIT  200;
  56.  
  57. QUERY EXPLAIN
  58. -------------
  59.  
  60. EXPLAIN SELECT id, date, floor(micro_ts /1000) as milli_ts, micro_ts,method,reply_reason,ruri,ruri_user,ruri_domain,from_user,from_domain,from_tag, to_user,to_domain,to_tag,pid_user,contact_user,auth_user,callid,callid_aleg,via_1,via_1_branch,cseq,diversion,reason,content_type,auth, user_agent,source_ip,source_port,destination_ip,destination_port,contact_ip,contact_port,originator_ip,originator_port,correlation_id,proto,family,rtp_stat,type,node,custom_field1,custom_field2,custom_field3,msg FROM sip_capture_call_20181219  WHERE (date BETWEEN FROM_UNIXTIME(1545231736) AND FROM_UNIXTIME(1545236093)) AND ( ( callid = '61858b0d6ccd67e60b3a9dfa5ea19b53@voips-ng.modulus.gr' OR callid = '459774299@192_168_1_8' )) ORDER BY id DESC  LIMIT 200;
  61. +------+-------------+---------------------------+-------+---------------+---------+---------+------+---------+-------------+
  62. | id   | select_type | table                     | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
  63. +------+-------------+---------------------------+-------+---------------+---------+---------+------+---------+-------------+
  64. |    1 | SIMPLE      | sip_capture_call_20181219 | index | date,callid   | PRIMARY | 12      | NULL | 3193959 | Using where |
  65. +------+-------------+---------------------------+-------+---------------+---------+---------+------+---------+-------------+
  66. 1 row in set (0.002 sec)
  67.  
  68. QUERY EXPLAIN WITHOUT ORDER BY CLAUSE
  69. -------------------------------------
  70.  
  71. EXPLAIN SELECT id, date, floor(micro_ts /1000) as milli_ts, micro_ts,method,reply_reason,ruri,ruri_user,ruri_domain,from_user,from_domain,from_tag, to_user,to_domain,to_tag,pid_user,contact_user,auth_user,callid,callid_aleg,via_1,via_1_branch,cseq,diversion,reason,content_type,auth, user_agent,source_ip,source_port,destination_ip,destination_port,contact_ip,contact_port,originator_ip,originator_port,correlation_id,proto,family,rtp_stat,type,node,custom_field1,custom_field2,custom_field3,msg FROM sip_capture_call_20181219  WHERE (date BETWEEN FROM_UNIXTIME(1545231736) AND FROM_UNIXTIME(1545236093)) AND ( ( callid = '61858b0d6ccd67e60b3a9dfa5ea19b53@voips-ng.modulus.gr' OR callid = '459774299@192_168_1_8' )) LIMIT 200;
  72. +------+-------------+---------------------------+-------+---------------+--------+---------+------+------+-------------+
  73. | id   | select_type | table                     | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
  74. +------+-------------+---------------------------+-------+---------------+--------+---------+------+------+-------------+
  75. |    1 | SIMPLE      | sip_capture_call_20181219 | range | date,callid   | callid | 362     | NULL |   44 | Using where |
  76. +------+-------------+---------------------------+-------+---------------+--------+---------+------+------+-------------+
  77. 1 row in set (0.002 sec)
  78.  
  79.  
  80. SHOW CREATE TABLE
  81. -----------------
  82.  
  83. show create table sip_capture_call_20181219;
  84. +---------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  85. | Table                     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
  86. +---------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  87. | sip_capture_call_20181219 | CREATE TABLE `sip_capture_call_20181219` (
  88.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  89.   `date` timestamp NOT NULL DEFAULT current_timestamp(),
  90.   `micro_ts` bigint(18) NOT NULL DEFAULT 0,
  91.   `method` varchar(50) NOT NULL DEFAULT '',
  92.   `reply_reason` varchar(100) NOT NULL DEFAULT '',
  93.   `ruri` varchar(200) NOT NULL DEFAULT '',
  94.   `ruri_user` varchar(100) NOT NULL DEFAULT '',
  95.   `ruri_domain` varchar(150) NOT NULL DEFAULT '',
  96.   `from_user` varchar(100) NOT NULL DEFAULT '',
  97.   `from_domain` varchar(150) NOT NULL DEFAULT '',
  98.   `from_tag` varchar(64) NOT NULL DEFAULT '',
  99.   `to_user` varchar(100) NOT NULL DEFAULT '',
  100.   `to_domain` varchar(150) NOT NULL DEFAULT '',
  101.   `to_tag` varchar(64) NOT NULL DEFAULT '',
  102.   `pid_user` varchar(100) NOT NULL DEFAULT '',
  103.   `contact_user` varchar(120) NOT NULL DEFAULT '',
  104.   `auth_user` varchar(120) NOT NULL DEFAULT '',
  105.   `callid` varchar(120) NOT NULL DEFAULT '',
  106.   `callid_aleg` varchar(120) NOT NULL DEFAULT '',
  107.   `via_1` varchar(256) NOT NULL DEFAULT '',
  108.   `via_1_branch` varchar(80) NOT NULL DEFAULT '',
  109.   `cseq` varchar(25) NOT NULL DEFAULT '',
  110.   `diversion` varchar(256) NOT NULL DEFAULT '',
  111.   `reason` varchar(200) NOT NULL DEFAULT '',
  112.   `content_type` varchar(256) NOT NULL DEFAULT '',
  113.   `auth` varchar(256) NOT NULL DEFAULT '',
  114.   `user_agent` varchar(256) NOT NULL DEFAULT '',
  115.   `source_ip` varchar(60) NOT NULL DEFAULT '',
  116.   `source_port` int(10) NOT NULL DEFAULT 0,
  117.   `destination_ip` varchar(60) NOT NULL DEFAULT '',
  118.   `destination_port` int(10) NOT NULL DEFAULT 0,
  119.   `contact_ip` varchar(60) NOT NULL DEFAULT '',
  120.   `contact_port` int(10) NOT NULL DEFAULT 0,
  121.   `originator_ip` varchar(60) NOT NULL DEFAULT '',
  122.   `originator_port` int(10) NOT NULL DEFAULT 0,
  123.   `expires` int(5) NOT NULL DEFAULT -1,
  124.   `correlation_id` varchar(256) NOT NULL DEFAULT '',
  125.   `custom_field1` varchar(120) NOT NULL DEFAULT '',
  126.   `custom_field2` varchar(120) NOT NULL DEFAULT '',
  127.   `custom_field3` varchar(120) NOT NULL DEFAULT '',
  128.   `proto` int(5) NOT NULL DEFAULT 0,
  129.   `family` int(1) DEFAULT NULL,
  130.   `rtp_stat` varchar(256) NOT NULL DEFAULT '',
  131.   `type` int(2) NOT NULL DEFAULT 0,
  132.   `node` varchar(125) NOT NULL DEFAULT '',
  133.   `msg` varchar(3000) NOT NULL DEFAULT '',
  134.   PRIMARY KEY (`id`,`date`),
  135.   KEY `ruri_user` (`ruri_user`),
  136.   KEY `from_user` (`from_user`),
  137.   KEY `to_user` (`to_user`),
  138.   KEY `pid_user` (`pid_user`),
  139.   KEY `auth_user` (`auth_user`),
  140.   KEY `callid_aleg` (`callid_aleg`),
  141.   KEY `date` (`date`),
  142.   KEY `callid` (`callid`),
  143.   KEY `method` (`method`),
  144.   KEY `source_ip` (`source_ip`)
  145. ) ENGINE=InnoDB AUTO_INCREMENT=9595781 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMMENT='20181219'
  146.  PARTITION BY RANGE (unix_timestamp(`date`))
  147. (PARTITION `p2018121900` VALUES LESS THAN (1545264000) ENGINE = InnoDB,
  148.  PARTITION `pmax` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) |
  149. +---------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  150. 1 row in set (0.001 sec)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!