Hi all,
I created a table with 10GB (60M records), added unique index (hidden_id)
manually after data insertion.
I had the simplest query but it took one minutes to complete.
select hidden_id from netflow where hidden_id = 350000;
And also the query took tens of minutes "select * from netflow order by
hidden_id limit 12500 offset 212500;".
It really confuses me.
I post the analysis of the first below. Any clue why it's so slow?
trace select hidden_id from netflow where hidden_id = 350000;
+----------+------------------------------------------------------------------+
| ticks | stmt
|
+==========+==================================================================+
| 3 | X_3 := sql.mvc();
|
| 15 | X_7=[69396995] :=
sql.bind(X_3=0,"sys","netflow","hidd |
: : en_id",0);
:
| 227 | X_4:bat[:oid,:oid] =[69396995] :=
sql.tid(X_3=0,"sys" |
: : ,"netflow");
:
| 72978741 | X_36=[1] :=
algebra.subselect(X_7=[69396995] |
: :
,X_4=:bat[:oid,:oid][69396995],A0=350000:lng,A0=35000 :
: : 0:lng,true,true,false);
:
| 17 | (X_10=[0],r1_10=[0]) :=
sql.bind(X_3=0,"sys" |
: : ,"netflow","hidden_id",2);
:
| 14 | X_37=[0] :=
algebra.subselect(r1_10=[0],A0= |
: : 350000:lng,A0=350000:lng,true,true,false);
:
| 6 | X_13=[0] :=
sql.bind(X_3=0,"sys","netflow","hidden_id" |
: : ,1);
:
| 15 | X_38=[0] :=
algebra.subselect(X_13=[0],X_4= |
: :
:bat[:oid,:oid][69396995],A0=350000:lng,A0=350000:lng :
: : ,true,true,false);
:
| 4 | X_15=[1] :=
sql.subdelta(X_36=[1],X_4=:bat[:oid,:oid][69396995],X_10=[0],X_37=[0],X_38=[0]);
:
| 20 | X_17=[1] :=
sql.projectdelta(X_15=[1],X_7=< |
: :
tmp_2510>[69396995],X_10=[0],r1_10=[0],X_13= :
: : [0]);
:
| 6 | X_18 := sql.resultSet(1,1,X_17=[1]);
|
| 7 |
sql.rsColumn(X_18=1,"sys.netflow","hidden_id","bigint",64,0,X_17 |
: : =[1]);
:
| 2 | X_23 := io.stdout();
|
| 25 | sql.exportResult(X_23=="104d2":streams,X_18=1);
|
| 1 | end s1_3;
|
| 73011629 | X_5:void := user.s1_3(350000:lng);
|
+----------+------------------------------------------------------------------+
This is the table being created.
CREATE TABLE "netflow" (
"time_seconds" double DEFAULT NULL,
"parsed_date" timestamp DEFAULT NULL,
"date_time_str" varchar(45) DEFAULT NULL,
"ip_layer_protocol" bigint DEFAULT NULL,
"ip_layer_protocol_code" varchar(45) DEFAULT NULL,
"first_seen_src_ip" varchar(45) DEFAULT NULL,
"first_seen_dest_ip" varchar(45) DEFAULT NULL,
"first_seen_src_port" bigint DEFAULT NULL,
"first_seen_dest_port" bigint DEFAULT NULL,
"more_fragments" varchar(45) DEFAULT NULL,
"cont_fragments" varchar(45) DEFAULT NULL,
"duration_seconds" bigint DEFAULT NULL,
"first_seen_src_payload_bytes" bigint DEFAULT NULL,
"first_seen_dest_payload_bytes" bigint DEFAULT NULL,
"first_seen_src_total_bytes" bigint DEFAULT NULL,
"first_seen_dest_total_bytes" bigint DEFAULT NULL,
"first_seen_src_packet_count" bigint DEFAULT NULL,
"first_seen_dest_packet_count" bigint DEFAULT NULL,
"record_force_out" varchar(45) DEFAULT NULL
);
Best regards,
Summer
