281 lines
59 KiB
Plaintext
281 lines
59 KiB
Plaintext
--echo // small dataset, fixed vector, tidb_rowid, no index
|
|
create table t(vec vector(3));
|
|
alter table t set tiflash replica 1;
|
|
insert into t values ('[1,2,3]'), ('[1,2,5]'), ('[1,2,1]');
|
|
analyze table t;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(10);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
alter table t compact;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // small dataset, fixed vector, tidb_rowid, indexed
|
|
create table t(vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values ('[1,2,3]'), ('[1,2,5]'), ('[1,2,1]');
|
|
analyze table t;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
alter table t compact;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // small dataset, fixed vector, int PK, no index
|
|
create table t(id int primary key, vec vector(3));
|
|
alter table t set tiflash replica 1;
|
|
insert into t values (1, '[1,2,3]'), (2, '[1,2,5]'), (3, '[1,2,1]');
|
|
analyze table t;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
alter table t compact;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // small dataset, fixed vector, int PK, indexed
|
|
create table t(id int primary key, vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values (1, '[1,2,3]'), (2, '[1,2,5]'), (3, '[1,2,1]');
|
|
analyze table t;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
#TiFlash nightly release not include https://github.com/pingcap/tiflash/pull/10103, don't run this sql
|
|
#select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
#explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
alter table t compact;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
drop table t;
|
|
|
|
--echo // small dataset, fixed vector, clustered char PK, indexed
|
|
create table t(id varchar(40) primary key CLUSTERED, vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values ('id1', '[1,2,3]'), ('id2', '[1,2,5]'), ('id3', '[1,2,1]');
|
|
analyze table t;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
#TiFlash nightly release not include https://github.com/pingcap/tiflash/pull/10103, don't run this sql
|
|
#select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
#explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
alter table t compact;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // small dataset, fixed vector, non-clustered char PK, indexed
|
|
create table t(id varchar(40) primary key NONCLUSTERED, vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values ('id1', '[1,2,3]'), ('id2', '[1,2,5]'), ('id3', '[1,2,1]');
|
|
analyze table t;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
#TiFlash nightly release not include https://github.com/pingcap/tiflash/pull/10103, don't run this sql
|
|
#select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
#explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
alter table t compact;
|
|
select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[0,0,0]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // large dataset, fixed vector, tidb_rowid, indexed
|
|
create table t(vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values ('[0,0,0]'),('[1,1,1]'),('[2,2,2]'),('[3,3,3]'),('[4,4,4]'),('[5,5,5]'),('[6,6,6]'),('[7,7,7]'),('[8,8,8]'),('[9,9,9]'),('[10,10,10]'),('[11,11,11]'),('[12,12,12]'),('[13,13,13]'),('[14,14,14]'),('[15,15,15]'),('[16,16,16]'),('[17,17,17]'),('[18,18,18]'),('[19,19,19]'),('[20,20,20]'),('[21,21,21]'),('[22,22,22]'),('[23,23,23]'),('[24,24,24]'),('[25,25,25]'),('[26,26,26]'),('[27,27,27]'),('[28,28,28]'),('[29,29,29]'),('[30,30,30]'),('[31,31,31]'),('[32,32,32]'),('[33,33,33]'),('[34,34,34]'),('[35,35,35]'),('[36,36,36]'),('[37,37,37]'),('[38,38,38]'),('[39,39,39]'),('[40,40,40]'),('[41,41,41]'),('[42,42,42]'),('[43,43,43]'),('[44,44,44]'),('[45,45,45]'),('[46,46,46]'),('[47,47,47]'),('[48,48,48]'),('[49,49,49]'),('[50,50,50]'),('[51,51,51]'),('[52,52,52]'),('[53,53,53]'),('[54,54,54]'),('[55,55,55]'),('[56,56,56]'),('[57,57,57]'),('[58,58,58]'),('[59,59,59]'),('[60,60,60]'),('[61,61,61]'),('[62,62,62]'),('[63,63,63]'),('[64,64,64]'),('[65,65,65]'),('[66,66,66]'),('[67,67,67]'),('[68,68,68]'),('[69,69,69]'),('[70,70,70]'),('[71,71,71]'),('[72,72,72]'),('[73,73,73]'),('[74,74,74]'),('[75,75,75]'),('[76,76,76]'),('[77,77,77]'),('[78,78,78]'),('[79,79,79]'),('[80,80,80]'),('[81,81,81]'),('[82,82,82]'),('[83,83,83]'),('[84,84,84]'),('[85,85,85]'),('[86,86,86]'),('[87,87,87]'),('[88,88,88]'),('[89,89,89]'),('[90,90,90]'),('[91,91,91]'),('[92,92,92]'),('[93,93,93]'),('[94,94,94]'),('[95,95,95]'),('[96,96,96]'),('[97,97,97]'),('[98,98,98]'),('[99,99,99]'),('[100,100,100]'),('[101,101,101]'),('[102,102,102]'),('[103,103,103]'),('[104,104,104]'),('[105,105,105]'),('[106,106,106]'),('[107,107,107]'),('[108,108,108]'),('[109,109,109]'),('[110,110,110]'),('[111,111,111]'),('[112,112,112]'),('[113,113,113]'),('[114,114,114]'),('[115,115,115]'),('[116,116,116]'),('[117,117,117]'),('[118,118,118]'),('[119,119,119]'),('[120,120,120]'),('[121,121,121]'),('[122,122,122]'),('[123,123,123]'),('[124,124,124]'),('[125,125,125]'),('[126,126,126]'),('[127,127,127]'),('[128,128,128]'),('[129,129,129]'),('[130,130,130]'),('[131,131,131]'),('[132,132,132]'),('[133,133,133]'),('[134,134,134]'),('[135,135,135]'),('[136,136,136]'),('[137,137,137]'),('[138,138,138]'),('[139,139,139]'),('[140,140,140]'),('[141,141,141]'),('[142,142,142]'),('[143,143,143]'),('[144,144,144]'),('[145,145,145]'),('[146,146,146]'),('[147,147,147]'),('[148,148,148]'),('[149,149,149]'),('[150,150,150]'),('[151,151,151]'),('[152,152,152]'),('[153,153,153]'),('[154,154,154]'),('[155,155,155]'),('[156,156,156]'),('[157,157,157]'),('[158,158,158]'),('[159,159,159]'),('[160,160,160]'),('[161,161,161]'),('[162,162,162]'),('[163,163,163]'),('[164,164,164]'),('[165,165,165]'),('[166,166,166]'),('[167,167,167]'),('[168,168,168]'),('[169,169,169]'),('[170,170,170]'),('[171,171,171]'),('[172,172,172]'),('[173,173,173]'),('[174,174,174]'),('[175,175,175]'),('[176,176,176]'),('[177,177,177]'),('[178,178,178]'),('[179,179,179]'),('[180,180,180]'),('[181,181,181]'),('[182,182,182]'),('[183,183,183]'),('[184,184,184]'),('[185,185,185]'),('[186,186,186]'),('[187,187,187]'),('[188,188,188]'),('[189,189,189]'),('[190,190,190]'),('[191,191,191]'),('[192,192,192]'),('[193,193,193]'),('[194,194,194]'),('[195,195,195]'),('[196,196,196]'),('[197,197,197]'),('[198,198,198]'),('[199,199,199]'),('[200,200,200]'),('[201,201,201]'),('[202,202,202]'),('[203,203,203]'),('[204,204,204]'),('[205,205,205]'),('[206,206,206]'),('[207,207,207]'),('[208,208,208]'),('[209,209,209]'),('[210,210,210]'),('[211,211,211]'),('[212,212,212]'),('[213,213,213]'),('[214,214,214]'),('[215,215,215]'),('[216,216,216]'),('[217,217,217]'),('[218,218,218]'),('[219,219,219]'),('[220,220,220]'),('[221,221,221]'),('[222,222,222]'),('[223,223,223]'),('[224,224,224]'),('[225,225,225]'),('[226,226,226]'),('[227,227,227]'),('[228,228,228]'),('[229,229,229]'),('[230,230,230]'),('[231,231,231]'),('[232,232,232]'),('[233,233,233]'),('[234,234,234]'),('[235,235,235]'),('[236,236,236]'),('[237,237,237]'),('[238,238,238]'),('[239,239,239]'),('[240,240,240]'),('[241,241,241]'),('[242,242,242]'),('[243,243,243]'),('[244,244,244]'),('[245,245,245]'),('[246,246,246]'),('[247,247,247]'),('[248,248,248]'),('[249,249,249]'),('[250,250,250]'),('[251,251,251]'),('[252,252,252]'),('[253,253,253]'),('[254,254,254]'),('[255,255,255]'),('[256,256,256]'),('[257,257,257]'),('[258,258,258]'),('[259,259,259]'),('[260,260,260]'),('[261,261,261]'),('[262,262,262]'),('[263,263,263]'),('[264,264,264]'),('[265,265,265]'),('[266,266,266]'),('[267,267,267]'),('[268,268,268]'),('[269,269,269]'),('[270,270,270]'),('[271,271,271]'),('[272,272,272]'),('[273,273,273]'),('[274,274,274]'),('[275,275,275]'),('[276,276,276]'),('[277,277,277]'),('[278,278,278]'),('[279,279,279]'),('[280,280,280]'),('[281,281,281]'),('[282,282,282]'),('[283,283,283]'),('[284,284,284]'),('[285,285,285]'),('[286,286,286]'),('[287,287,287]'),('[288,288,288]'),('[289,289,289]'),('[290,290,290]'),('[291,291,291]'),('[292,292,292]'),('[293,293,293]'),('[294,294,294]'),('[295,295,295]'),('[296,296,296]'),('[297,297,297]'),('[298,298,298]'),('[299,299,299]'),('[300,300,300]'),('[301,301,301]'),('[302,302,302]'),('[303,303,303]'),('[304,304,304]'),('[305,305,305]'),('[306,306,306]'),('[307,307,307]'),('[308,308,308]'),('[309,309,309]'),('[310,310,310]'),('[311,311,311]'),('[312,312,312]'),('[313,313,313]'),('[314,314,314]'),('[315,315,315]'),('[316,316,316]'),('[317,317,317]'),('[318,318,318]'),('[319,319,319]'),('[320,320,320]'),('[321,321,321]'),('[322,322,322]'),('[323,323,323]'),('[324,324,324]'),('[325,325,325]'),('[326,326,326]'),('[327,327,327]'),('[328,328,328]'),('[329,329,329]'),('[330,330,330]'),('[331,331,331]'),('[332,332,332]'),('[333,333,333]'),('[334,334,334]'),('[335,335,335]'),('[336,336,336]'),('[337,337,337]'),('[338,338,338]'),('[339,339,339]'),('[340,340,340]'),('[341,341,341]'),('[342,342,342]'),('[343,343,343]'),('[344,344,344]'),('[345,345,345]'),('[346,346,346]'),('[347,347,347]'),('[348,348,348]'),('[349,349,349]'),('[350,350,350]'),('[351,351,351]'),('[352,352,352]'),('[353,353,353]'),('[354,354,354]'),('[355,355,355]'),('[356,356,356]'),('[357,357,357]'),('[358,358,358]'),('[359,359,359]'),('[360,360,360]'),('[361,361,361]'),('[362,362,362]'),('[363,363,363]'),('[364,364,364]'),('[365,365,365]'),('[366,366,366]'),('[367,367,367]'),('[368,368,368]'),('[369,369,369]'),('[370,370,370]'),('[371,371,371]'),('[372,372,372]'),('[373,373,373]'),('[374,374,374]'),('[375,375,375]'),('[376,376,376]'),('[377,377,377]'),('[378,378,378]'),('[379,379,379]'),('[380,380,380]'),('[381,381,381]'),('[382,382,382]'),('[383,383,383]'),('[384,384,384]'),('[385,385,385]'),('[386,386,386]'),('[387,387,387]'),('[388,388,388]'),('[389,389,389]'),('[390,390,390]'),('[391,391,391]'),('[392,392,392]'),('[393,393,393]'),('[394,394,394]'),('[395,395,395]'),('[396,396,396]'),('[397,397,397]'),('[398,398,398]'),('[399,399,399]'),('[400,400,400]'),('[401,401,401]'),('[402,402,402]'),('[403,403,403]'),('[404,404,404]'),('[405,405,405]'),('[406,406,406]'),('[407,407,407]'),('[408,408,408]'),('[409,409,409]'),('[410,410,410]'),('[411,411,411]'),('[412,412,412]'),('[413,413,413]'),('[414,414,414]'),('[415,415,415]'),('[416,416,416]'),('[417,417,417]'),('[418,418,418]'),('[419,419,419]'),('[420,420,420]'),('[421,421,421]'),('[422,422,422]'),('[423,423,423]'),('[424,424,424]'),('[425,425,425]'),('[426,426,426]'),('[427,427,427]'),('[428,428,428]'),('[429,429,429]'),('[430,430,430]'),('[431,431,431]'),('[432,432,432]'),('[433,433,433]'),('[434,434,434]'),('[435,435,435]'),('[436,436,436]'),('[437,437,437]'),('[438,438,438]'),('[439,439,439]'),('[440,440,440]'),('[441,441,441]'),('[442,442,442]'),('[443,443,443]'),('[444,444,444]'),('[445,445,445]'),('[446,446,446]'),('[447,447,447]'),('[448,448,448]'),('[449,449,449]'),('[450,450,450]'),('[451,451,451]'),('[452,452,452]'),('[453,453,453]'),('[454,454,454]'),('[455,455,455]'),('[456,456,456]'),('[457,457,457]'),('[458,458,458]'),('[459,459,459]'),('[460,460,460]'),('[461,461,461]'),('[462,462,462]'),('[463,463,463]'),('[464,464,464]'),('[465,465,465]'),('[466,466,466]'),('[467,467,467]'),('[468,468,468]'),('[469,469,469]'),('[470,470,470]'),('[471,471,471]'),('[472,472,472]'),('[473,473,473]'),('[474,474,474]'),('[475,475,475]'),('[476,476,476]'),('[477,477,477]'),('[478,478,478]'),('[479,479,479]'),('[480,480,480]'),('[481,481,481]'),('[482,482,482]'),('[483,483,483]'),('[484,484,484]'),('[485,485,485]'),('[486,486,486]'),('[487,487,487]'),('[488,488,488]'),('[489,489,489]'),('[490,490,490]'),('[491,491,491]'),('[492,492,492]'),('[493,493,493]'),('[494,494,494]'),('[495,495,495]'),('[496,496,496]'),('[497,497,497]'),('[498,498,498]'),('[499,499,499]');
|
|
analyze table t;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
alter table t compact;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // large dataset, fixed vector, int PK, indexed
|
|
create table t(id int primary key, vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values (0,'[0,0,0]'),(1,'[1,1,1]'),(2,'[2,2,2]'),(3,'[3,3,3]'),(4,'[4,4,4]'),(5,'[5,5,5]'),(6,'[6,6,6]'),(7,'[7,7,7]'),(8,'[8,8,8]'),(9,'[9,9,9]'),(10,'[10,10,10]'),(11,'[11,11,11]'),(12,'[12,12,12]'),(13,'[13,13,13]'),(14,'[14,14,14]'),(15,'[15,15,15]'),(16,'[16,16,16]'),(17,'[17,17,17]'),(18,'[18,18,18]'),(19,'[19,19,19]'),(20,'[20,20,20]'),(21,'[21,21,21]'),(22,'[22,22,22]'),(23,'[23,23,23]'),(24,'[24,24,24]'),(25,'[25,25,25]'),(26,'[26,26,26]'),(27,'[27,27,27]'),(28,'[28,28,28]'),(29,'[29,29,29]'),(30,'[30,30,30]'),(31,'[31,31,31]'),(32,'[32,32,32]'),(33,'[33,33,33]'),(34,'[34,34,34]'),(35,'[35,35,35]'),(36,'[36,36,36]'),(37,'[37,37,37]'),(38,'[38,38,38]'),(39,'[39,39,39]'),(40,'[40,40,40]'),(41,'[41,41,41]'),(42,'[42,42,42]'),(43,'[43,43,43]'),(44,'[44,44,44]'),(45,'[45,45,45]'),(46,'[46,46,46]'),(47,'[47,47,47]'),(48,'[48,48,48]'),(49,'[49,49,49]'),(50,'[50,50,50]'),(51,'[51,51,51]'),(52,'[52,52,52]'),(53,'[53,53,53]'),(54,'[54,54,54]'),(55,'[55,55,55]'),(56,'[56,56,56]'),(57,'[57,57,57]'),(58,'[58,58,58]'),(59,'[59,59,59]'),(60,'[60,60,60]'),(61,'[61,61,61]'),(62,'[62,62,62]'),(63,'[63,63,63]'),(64,'[64,64,64]'),(65,'[65,65,65]'),(66,'[66,66,66]'),(67,'[67,67,67]'),(68,'[68,68,68]'),(69,'[69,69,69]'),(70,'[70,70,70]'),(71,'[71,71,71]'),(72,'[72,72,72]'),(73,'[73,73,73]'),(74,'[74,74,74]'),(75,'[75,75,75]'),(76,'[76,76,76]'),(77,'[77,77,77]'),(78,'[78,78,78]'),(79,'[79,79,79]'),(80,'[80,80,80]'),(81,'[81,81,81]'),(82,'[82,82,82]'),(83,'[83,83,83]'),(84,'[84,84,84]'),(85,'[85,85,85]'),(86,'[86,86,86]'),(87,'[87,87,87]'),(88,'[88,88,88]'),(89,'[89,89,89]'),(90,'[90,90,90]'),(91,'[91,91,91]'),(92,'[92,92,92]'),(93,'[93,93,93]'),(94,'[94,94,94]'),(95,'[95,95,95]'),(96,'[96,96,96]'),(97,'[97,97,97]'),(98,'[98,98,98]'),(99,'[99,99,99]'),(100,'[100,100,100]'),(101,'[101,101,101]'),(102,'[102,102,102]'),(103,'[103,103,103]'),(104,'[104,104,104]'),(105,'[105,105,105]'),(106,'[106,106,106]'),(107,'[107,107,107]'),(108,'[108,108,108]'),(109,'[109,109,109]'),(110,'[110,110,110]'),(111,'[111,111,111]'),(112,'[112,112,112]'),(113,'[113,113,113]'),(114,'[114,114,114]'),(115,'[115,115,115]'),(116,'[116,116,116]'),(117,'[117,117,117]'),(118,'[118,118,118]'),(119,'[119,119,119]'),(120,'[120,120,120]'),(121,'[121,121,121]'),(122,'[122,122,122]'),(123,'[123,123,123]'),(124,'[124,124,124]'),(125,'[125,125,125]'),(126,'[126,126,126]'),(127,'[127,127,127]'),(128,'[128,128,128]'),(129,'[129,129,129]'),(130,'[130,130,130]'),(131,'[131,131,131]'),(132,'[132,132,132]'),(133,'[133,133,133]'),(134,'[134,134,134]'),(135,'[135,135,135]'),(136,'[136,136,136]'),(137,'[137,137,137]'),(138,'[138,138,138]'),(139,'[139,139,139]'),(140,'[140,140,140]'),(141,'[141,141,141]'),(142,'[142,142,142]'),(143,'[143,143,143]'),(144,'[144,144,144]'),(145,'[145,145,145]'),(146,'[146,146,146]'),(147,'[147,147,147]'),(148,'[148,148,148]'),(149,'[149,149,149]'),(150,'[150,150,150]'),(151,'[151,151,151]'),(152,'[152,152,152]'),(153,'[153,153,153]'),(154,'[154,154,154]'),(155,'[155,155,155]'),(156,'[156,156,156]'),(157,'[157,157,157]'),(158,'[158,158,158]'),(159,'[159,159,159]'),(160,'[160,160,160]'),(161,'[161,161,161]'),(162,'[162,162,162]'),(163,'[163,163,163]'),(164,'[164,164,164]'),(165,'[165,165,165]'),(166,'[166,166,166]'),(167,'[167,167,167]'),(168,'[168,168,168]'),(169,'[169,169,169]'),(170,'[170,170,170]'),(171,'[171,171,171]'),(172,'[172,172,172]'),(173,'[173,173,173]'),(174,'[174,174,174]'),(175,'[175,175,175]'),(176,'[176,176,176]'),(177,'[177,177,177]'),(178,'[178,178,178]'),(179,'[179,179,179]'),(180,'[180,180,180]'),(181,'[181,181,181]'),(182,'[182,182,182]'),(183,'[183,183,183]'),(184,'[184,184,184]'),(185,'[185,185,185]'),(186,'[186,186,186]'),(187,'[187,187,187]'),(188,'[188,188,188]'),(189,'[189,189,189]'),(190,'[190,190,190]'),(191,'[191,191,191]'),(192,'[192,192,192]'),(193,'[193,193,193]'),(194,'[194,194,194]'),(195,'[195,195,195]'),(196,'[196,196,196]'),(197,'[197,197,197]'),(198,'[198,198,198]'),(199,'[199,199,199]'),(200,'[200,200,200]'),(201,'[201,201,201]'),(202,'[202,202,202]'),(203,'[203,203,203]'),(204,'[204,204,204]'),(205,'[205,205,205]'),(206,'[206,206,206]'),(207,'[207,207,207]'),(208,'[208,208,208]'),(209,'[209,209,209]'),(210,'[210,210,210]'),(211,'[211,211,211]'),(212,'[212,212,212]'),(213,'[213,213,213]'),(214,'[214,214,214]'),(215,'[215,215,215]'),(216,'[216,216,216]'),(217,'[217,217,217]'),(218,'[218,218,218]'),(219,'[219,219,219]'),(220,'[220,220,220]'),(221,'[221,221,221]'),(222,'[222,222,222]'),(223,'[223,223,223]'),(224,'[224,224,224]'),(225,'[225,225,225]'),(226,'[226,226,226]'),(227,'[227,227,227]'),(228,'[228,228,228]'),(229,'[229,229,229]'),(230,'[230,230,230]'),(231,'[231,231,231]'),(232,'[232,232,232]'),(233,'[233,233,233]'),(234,'[234,234,234]'),(235,'[235,235,235]'),(236,'[236,236,236]'),(237,'[237,237,237]'),(238,'[238,238,238]'),(239,'[239,239,239]'),(240,'[240,240,240]'),(241,'[241,241,241]'),(242,'[242,242,242]'),(243,'[243,243,243]'),(244,'[244,244,244]'),(245,'[245,245,245]'),(246,'[246,246,246]'),(247,'[247,247,247]'),(248,'[248,248,248]'),(249,'[249,249,249]'),(250,'[250,250,250]'),(251,'[251,251,251]'),(252,'[252,252,252]'),(253,'[253,253,253]'),(254,'[254,254,254]'),(255,'[255,255,255]'),(256,'[256,256,256]'),(257,'[257,257,257]'),(258,'[258,258,258]'),(259,'[259,259,259]'),(260,'[260,260,260]'),(261,'[261,261,261]'),(262,'[262,262,262]'),(263,'[263,263,263]'),(264,'[264,264,264]'),(265,'[265,265,265]'),(266,'[266,266,266]'),(267,'[267,267,267]'),(268,'[268,268,268]'),(269,'[269,269,269]'),(270,'[270,270,270]'),(271,'[271,271,271]'),(272,'[272,272,272]'),(273,'[273,273,273]'),(274,'[274,274,274]'),(275,'[275,275,275]'),(276,'[276,276,276]'),(277,'[277,277,277]'),(278,'[278,278,278]'),(279,'[279,279,279]'),(280,'[280,280,280]'),(281,'[281,281,281]'),(282,'[282,282,282]'),(283,'[283,283,283]'),(284,'[284,284,284]'),(285,'[285,285,285]'),(286,'[286,286,286]'),(287,'[287,287,287]'),(288,'[288,288,288]'),(289,'[289,289,289]'),(290,'[290,290,290]'),(291,'[291,291,291]'),(292,'[292,292,292]'),(293,'[293,293,293]'),(294,'[294,294,294]'),(295,'[295,295,295]'),(296,'[296,296,296]'),(297,'[297,297,297]'),(298,'[298,298,298]'),(299,'[299,299,299]'),(300,'[300,300,300]'),(301,'[301,301,301]'),(302,'[302,302,302]'),(303,'[303,303,303]'),(304,'[304,304,304]'),(305,'[305,305,305]'),(306,'[306,306,306]'),(307,'[307,307,307]'),(308,'[308,308,308]'),(309,'[309,309,309]'),(310,'[310,310,310]'),(311,'[311,311,311]'),(312,'[312,312,312]'),(313,'[313,313,313]'),(314,'[314,314,314]'),(315,'[315,315,315]'),(316,'[316,316,316]'),(317,'[317,317,317]'),(318,'[318,318,318]'),(319,'[319,319,319]'),(320,'[320,320,320]'),(321,'[321,321,321]'),(322,'[322,322,322]'),(323,'[323,323,323]'),(324,'[324,324,324]'),(325,'[325,325,325]'),(326,'[326,326,326]'),(327,'[327,327,327]'),(328,'[328,328,328]'),(329,'[329,329,329]'),(330,'[330,330,330]'),(331,'[331,331,331]'),(332,'[332,332,332]'),(333,'[333,333,333]'),(334,'[334,334,334]'),(335,'[335,335,335]'),(336,'[336,336,336]'),(337,'[337,337,337]'),(338,'[338,338,338]'),(339,'[339,339,339]'),(340,'[340,340,340]'),(341,'[341,341,341]'),(342,'[342,342,342]'),(343,'[343,343,343]'),(344,'[344,344,344]'),(345,'[345,345,345]'),(346,'[346,346,346]'),(347,'[347,347,347]'),(348,'[348,348,348]'),(349,'[349,349,349]'),(350,'[350,350,350]'),(351,'[351,351,351]'),(352,'[352,352,352]'),(353,'[353,353,353]'),(354,'[354,354,354]'),(355,'[355,355,355]'),(356,'[356,356,356]'),(357,'[357,357,357]'),(358,'[358,358,358]'),(359,'[359,359,359]'),(360,'[360,360,360]'),(361,'[361,361,361]'),(362,'[362,362,362]'),(363,'[363,363,363]'),(364,'[364,364,364]'),(365,'[365,365,365]'),(366,'[366,366,366]'),(367,'[367,367,367]'),(368,'[368,368,368]'),(369,'[369,369,369]'),(370,'[370,370,370]'),(371,'[371,371,371]'),(372,'[372,372,372]'),(373,'[373,373,373]'),(374,'[374,374,374]'),(375,'[375,375,375]'),(376,'[376,376,376]'),(377,'[377,377,377]'),(378,'[378,378,378]'),(379,'[379,379,379]'),(380,'[380,380,380]'),(381,'[381,381,381]'),(382,'[382,382,382]'),(383,'[383,383,383]'),(384,'[384,384,384]'),(385,'[385,385,385]'),(386,'[386,386,386]'),(387,'[387,387,387]'),(388,'[388,388,388]'),(389,'[389,389,389]'),(390,'[390,390,390]'),(391,'[391,391,391]'),(392,'[392,392,392]'),(393,'[393,393,393]'),(394,'[394,394,394]'),(395,'[395,395,395]'),(396,'[396,396,396]'),(397,'[397,397,397]'),(398,'[398,398,398]'),(399,'[399,399,399]'),(400,'[400,400,400]'),(401,'[401,401,401]'),(402,'[402,402,402]'),(403,'[403,403,403]'),(404,'[404,404,404]'),(405,'[405,405,405]'),(406,'[406,406,406]'),(407,'[407,407,407]'),(408,'[408,408,408]'),(409,'[409,409,409]'),(410,'[410,410,410]'),(411,'[411,411,411]'),(412,'[412,412,412]'),(413,'[413,413,413]'),(414,'[414,414,414]'),(415,'[415,415,415]'),(416,'[416,416,416]'),(417,'[417,417,417]'),(418,'[418,418,418]'),(419,'[419,419,419]'),(420,'[420,420,420]'),(421,'[421,421,421]'),(422,'[422,422,422]'),(423,'[423,423,423]'),(424,'[424,424,424]'),(425,'[425,425,425]'),(426,'[426,426,426]'),(427,'[427,427,427]'),(428,'[428,428,428]'),(429,'[429,429,429]'),(430,'[430,430,430]'),(431,'[431,431,431]'),(432,'[432,432,432]'),(433,'[433,433,433]'),(434,'[434,434,434]'),(435,'[435,435,435]'),(436,'[436,436,436]'),(437,'[437,437,437]'),(438,'[438,438,438]'),(439,'[439,439,439]'),(440,'[440,440,440]'),(441,'[441,441,441]'),(442,'[442,442,442]'),(443,'[443,443,443]'),(444,'[444,444,444]'),(445,'[445,445,445]'),(446,'[446,446,446]'),(447,'[447,447,447]'),(448,'[448,448,448]'),(449,'[449,449,449]'),(450,'[450,450,450]'),(451,'[451,451,451]'),(452,'[452,452,452]'),(453,'[453,453,453]'),(454,'[454,454,454]'),(455,'[455,455,455]'),(456,'[456,456,456]'),(457,'[457,457,457]'),(458,'[458,458,458]'),(459,'[459,459,459]'),(460,'[460,460,460]'),(461,'[461,461,461]'),(462,'[462,462,462]'),(463,'[463,463,463]'),(464,'[464,464,464]'),(465,'[465,465,465]'),(466,'[466,466,466]'),(467,'[467,467,467]'),(468,'[468,468,468]'),(469,'[469,469,469]'),(470,'[470,470,470]'),(471,'[471,471,471]'),(472,'[472,472,472]'),(473,'[473,473,473]'),(474,'[474,474,474]'),(475,'[475,475,475]'),(476,'[476,476,476]'),(477,'[477,477,477]'),(478,'[478,478,478]'),(479,'[479,479,479]'),(480,'[480,480,480]'),(481,'[481,481,481]'),(482,'[482,482,482]'),(483,'[483,483,483]'),(484,'[484,484,484]'),(485,'[485,485,485]'),(486,'[486,486,486]'),(487,'[487,487,487]'),(488,'[488,488,488]'),(489,'[489,489,489]'),(490,'[490,490,490]'),(491,'[491,491,491]'),(492,'[492,492,492]'),(493,'[493,493,493]'),(494,'[494,494,494]'),(495,'[495,495,495]'),(496,'[496,496,496]'),(497,'[497,497,497]'),(498,'[498,498,498]'),(499,'[499,499,499]');
|
|
analyze table t;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
#TiFlash nightly release not include https://github.com/pingcap/tiflash/pull/10103, don't run this sql
|
|
#select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
#explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
alter table t compact;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
#TiFlash nightly release not include https://github.com/pingcap/tiflash/pull/10103, don't run this sql
|
|
#select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
#explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // large dataset, fixed vector, clustered char PK, indexed
|
|
create table t(id varchar(40) primary key CLUSTERED, vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values ('id0','[0,0,0]'),('id1','[1,1,1]'),('id2','[2,2,2]'),('id3','[3,3,3]'),('id4','[4,4,4]'),('id5','[5,5,5]'),('id6','[6,6,6]'),('id7','[7,7,7]'),('id8','[8,8,8]'),('id9','[9,9,9]'),('id10','[10,10,10]'),('id11','[11,11,11]'),('id12','[12,12,12]'),('id13','[13,13,13]'),('id14','[14,14,14]'),('id15','[15,15,15]'),('id16','[16,16,16]'),('id17','[17,17,17]'),('id18','[18,18,18]'),('id19','[19,19,19]'),('id20','[20,20,20]'),('id21','[21,21,21]'),('id22','[22,22,22]'),('id23','[23,23,23]'),('id24','[24,24,24]'),('id25','[25,25,25]'),('id26','[26,26,26]'),('id27','[27,27,27]'),('id28','[28,28,28]'),('id29','[29,29,29]'),('id30','[30,30,30]'),('id31','[31,31,31]'),('id32','[32,32,32]'),('id33','[33,33,33]'),('id34','[34,34,34]'),('id35','[35,35,35]'),('id36','[36,36,36]'),('id37','[37,37,37]'),('id38','[38,38,38]'),('id39','[39,39,39]'),('id40','[40,40,40]'),('id41','[41,41,41]'),('id42','[42,42,42]'),('id43','[43,43,43]'),('id44','[44,44,44]'),('id45','[45,45,45]'),('id46','[46,46,46]'),('id47','[47,47,47]'),('id48','[48,48,48]'),('id49','[49,49,49]'),('id50','[50,50,50]'),('id51','[51,51,51]'),('id52','[52,52,52]'),('id53','[53,53,53]'),('id54','[54,54,54]'),('id55','[55,55,55]'),('id56','[56,56,56]'),('id57','[57,57,57]'),('id58','[58,58,58]'),('id59','[59,59,59]'),('id60','[60,60,60]'),('id61','[61,61,61]'),('id62','[62,62,62]'),('id63','[63,63,63]'),('id64','[64,64,64]'),('id65','[65,65,65]'),('id66','[66,66,66]'),('id67','[67,67,67]'),('id68','[68,68,68]'),('id69','[69,69,69]'),('id70','[70,70,70]'),('id71','[71,71,71]'),('id72','[72,72,72]'),('id73','[73,73,73]'),('id74','[74,74,74]'),('id75','[75,75,75]'),('id76','[76,76,76]'),('id77','[77,77,77]'),('id78','[78,78,78]'),('id79','[79,79,79]'),('id80','[80,80,80]'),('id81','[81,81,81]'),('id82','[82,82,82]'),('id83','[83,83,83]'),('id84','[84,84,84]'),('id85','[85,85,85]'),('id86','[86,86,86]'),('id87','[87,87,87]'),('id88','[88,88,88]'),('id89','[89,89,89]'),('id90','[90,90,90]'),('id91','[91,91,91]'),('id92','[92,92,92]'),('id93','[93,93,93]'),('id94','[94,94,94]'),('id95','[95,95,95]'),('id96','[96,96,96]'),('id97','[97,97,97]'),('id98','[98,98,98]'),('id99','[99,99,99]'),('id100','[100,100,100]'),('id101','[101,101,101]'),('id102','[102,102,102]'),('id103','[103,103,103]'),('id104','[104,104,104]'),('id105','[105,105,105]'),('id106','[106,106,106]'),('id107','[107,107,107]'),('id108','[108,108,108]'),('id109','[109,109,109]'),('id110','[110,110,110]'),('id111','[111,111,111]'),('id112','[112,112,112]'),('id113','[113,113,113]'),('id114','[114,114,114]'),('id115','[115,115,115]'),('id116','[116,116,116]'),('id117','[117,117,117]'),('id118','[118,118,118]'),('id119','[119,119,119]'),('id120','[120,120,120]'),('id121','[121,121,121]'),('id122','[122,122,122]'),('id123','[123,123,123]'),('id124','[124,124,124]'),('id125','[125,125,125]'),('id126','[126,126,126]'),('id127','[127,127,127]'),('id128','[128,128,128]'),('id129','[129,129,129]'),('id130','[130,130,130]'),('id131','[131,131,131]'),('id132','[132,132,132]'),('id133','[133,133,133]'),('id134','[134,134,134]'),('id135','[135,135,135]'),('id136','[136,136,136]'),('id137','[137,137,137]'),('id138','[138,138,138]'),('id139','[139,139,139]'),('id140','[140,140,140]'),('id141','[141,141,141]'),('id142','[142,142,142]'),('id143','[143,143,143]'),('id144','[144,144,144]'),('id145','[145,145,145]'),('id146','[146,146,146]'),('id147','[147,147,147]'),('id148','[148,148,148]'),('id149','[149,149,149]'),('id150','[150,150,150]'),('id151','[151,151,151]'),('id152','[152,152,152]'),('id153','[153,153,153]'),('id154','[154,154,154]'),('id155','[155,155,155]'),('id156','[156,156,156]'),('id157','[157,157,157]'),('id158','[158,158,158]'),('id159','[159,159,159]'),('id160','[160,160,160]'),('id161','[161,161,161]'),('id162','[162,162,162]'),('id163','[163,163,163]'),('id164','[164,164,164]'),('id165','[165,165,165]'),('id166','[166,166,166]'),('id167','[167,167,167]'),('id168','[168,168,168]'),('id169','[169,169,169]'),('id170','[170,170,170]'),('id171','[171,171,171]'),('id172','[172,172,172]'),('id173','[173,173,173]'),('id174','[174,174,174]'),('id175','[175,175,175]'),('id176','[176,176,176]'),('id177','[177,177,177]'),('id178','[178,178,178]'),('id179','[179,179,179]'),('id180','[180,180,180]'),('id181','[181,181,181]'),('id182','[182,182,182]'),('id183','[183,183,183]'),('id184','[184,184,184]'),('id185','[185,185,185]'),('id186','[186,186,186]'),('id187','[187,187,187]'),('id188','[188,188,188]'),('id189','[189,189,189]'),('id190','[190,190,190]'),('id191','[191,191,191]'),('id192','[192,192,192]'),('id193','[193,193,193]'),('id194','[194,194,194]'),('id195','[195,195,195]'),('id196','[196,196,196]'),('id197','[197,197,197]'),('id198','[198,198,198]'),('id199','[199,199,199]'),('id200','[200,200,200]'),('id201','[201,201,201]'),('id202','[202,202,202]'),('id203','[203,203,203]'),('id204','[204,204,204]'),('id205','[205,205,205]'),('id206','[206,206,206]'),('id207','[207,207,207]'),('id208','[208,208,208]'),('id209','[209,209,209]'),('id210','[210,210,210]'),('id211','[211,211,211]'),('id212','[212,212,212]'),('id213','[213,213,213]'),('id214','[214,214,214]'),('id215','[215,215,215]'),('id216','[216,216,216]'),('id217','[217,217,217]'),('id218','[218,218,218]'),('id219','[219,219,219]'),('id220','[220,220,220]'),('id221','[221,221,221]'),('id222','[222,222,222]'),('id223','[223,223,223]'),('id224','[224,224,224]'),('id225','[225,225,225]'),('id226','[226,226,226]'),('id227','[227,227,227]'),('id228','[228,228,228]'),('id229','[229,229,229]'),('id230','[230,230,230]'),('id231','[231,231,231]'),('id232','[232,232,232]'),('id233','[233,233,233]'),('id234','[234,234,234]'),('id235','[235,235,235]'),('id236','[236,236,236]'),('id237','[237,237,237]'),('id238','[238,238,238]'),('id239','[239,239,239]'),('id240','[240,240,240]'),('id241','[241,241,241]'),('id242','[242,242,242]'),('id243','[243,243,243]'),('id244','[244,244,244]'),('id245','[245,245,245]'),('id246','[246,246,246]'),('id247','[247,247,247]'),('id248','[248,248,248]'),('id249','[249,249,249]'),('id250','[250,250,250]'),('id251','[251,251,251]'),('id252','[252,252,252]'),('id253','[253,253,253]'),('id254','[254,254,254]'),('id255','[255,255,255]'),('id256','[256,256,256]'),('id257','[257,257,257]'),('id258','[258,258,258]'),('id259','[259,259,259]'),('id260','[260,260,260]'),('id261','[261,261,261]'),('id262','[262,262,262]'),('id263','[263,263,263]'),('id264','[264,264,264]'),('id265','[265,265,265]'),('id266','[266,266,266]'),('id267','[267,267,267]'),('id268','[268,268,268]'),('id269','[269,269,269]'),('id270','[270,270,270]'),('id271','[271,271,271]'),('id272','[272,272,272]'),('id273','[273,273,273]'),('id274','[274,274,274]'),('id275','[275,275,275]'),('id276','[276,276,276]'),('id277','[277,277,277]'),('id278','[278,278,278]'),('id279','[279,279,279]'),('id280','[280,280,280]'),('id281','[281,281,281]'),('id282','[282,282,282]'),('id283','[283,283,283]'),('id284','[284,284,284]'),('id285','[285,285,285]'),('id286','[286,286,286]'),('id287','[287,287,287]'),('id288','[288,288,288]'),('id289','[289,289,289]'),('id290','[290,290,290]'),('id291','[291,291,291]'),('id292','[292,292,292]'),('id293','[293,293,293]'),('id294','[294,294,294]'),('id295','[295,295,295]'),('id296','[296,296,296]'),('id297','[297,297,297]'),('id298','[298,298,298]'),('id299','[299,299,299]'),('id300','[300,300,300]'),('id301','[301,301,301]'),('id302','[302,302,302]'),('id303','[303,303,303]'),('id304','[304,304,304]'),('id305','[305,305,305]'),('id306','[306,306,306]'),('id307','[307,307,307]'),('id308','[308,308,308]'),('id309','[309,309,309]'),('id310','[310,310,310]'),('id311','[311,311,311]'),('id312','[312,312,312]'),('id313','[313,313,313]'),('id314','[314,314,314]'),('id315','[315,315,315]'),('id316','[316,316,316]'),('id317','[317,317,317]'),('id318','[318,318,318]'),('id319','[319,319,319]'),('id320','[320,320,320]'),('id321','[321,321,321]'),('id322','[322,322,322]'),('id323','[323,323,323]'),('id324','[324,324,324]'),('id325','[325,325,325]'),('id326','[326,326,326]'),('id327','[327,327,327]'),('id328','[328,328,328]'),('id329','[329,329,329]'),('id330','[330,330,330]'),('id331','[331,331,331]'),('id332','[332,332,332]'),('id333','[333,333,333]'),('id334','[334,334,334]'),('id335','[335,335,335]'),('id336','[336,336,336]'),('id337','[337,337,337]'),('id338','[338,338,338]'),('id339','[339,339,339]'),('id340','[340,340,340]'),('id341','[341,341,341]'),('id342','[342,342,342]'),('id343','[343,343,343]'),('id344','[344,344,344]'),('id345','[345,345,345]'),('id346','[346,346,346]'),('id347','[347,347,347]'),('id348','[348,348,348]'),('id349','[349,349,349]'),('id350','[350,350,350]'),('id351','[351,351,351]'),('id352','[352,352,352]'),('id353','[353,353,353]'),('id354','[354,354,354]'),('id355','[355,355,355]'),('id356','[356,356,356]'),('id357','[357,357,357]'),('id358','[358,358,358]'),('id359','[359,359,359]'),('id360','[360,360,360]'),('id361','[361,361,361]'),('id362','[362,362,362]'),('id363','[363,363,363]'),('id364','[364,364,364]'),('id365','[365,365,365]'),('id366','[366,366,366]'),('id367','[367,367,367]'),('id368','[368,368,368]'),('id369','[369,369,369]'),('id370','[370,370,370]'),('id371','[371,371,371]'),('id372','[372,372,372]'),('id373','[373,373,373]'),('id374','[374,374,374]'),('id375','[375,375,375]'),('id376','[376,376,376]'),('id377','[377,377,377]'),('id378','[378,378,378]'),('id379','[379,379,379]'),('id380','[380,380,380]'),('id381','[381,381,381]'),('id382','[382,382,382]'),('id383','[383,383,383]'),('id384','[384,384,384]'),('id385','[385,385,385]'),('id386','[386,386,386]'),('id387','[387,387,387]'),('id388','[388,388,388]'),('id389','[389,389,389]'),('id390','[390,390,390]'),('id391','[391,391,391]'),('id392','[392,392,392]'),('id393','[393,393,393]'),('id394','[394,394,394]'),('id395','[395,395,395]'),('id396','[396,396,396]'),('id397','[397,397,397]'),('id398','[398,398,398]'),('id399','[399,399,399]'),('id400','[400,400,400]'),('id401','[401,401,401]'),('id402','[402,402,402]'),('id403','[403,403,403]'),('id404','[404,404,404]'),('id405','[405,405,405]'),('id406','[406,406,406]'),('id407','[407,407,407]'),('id408','[408,408,408]'),('id409','[409,409,409]'),('id410','[410,410,410]'),('id411','[411,411,411]'),('id412','[412,412,412]'),('id413','[413,413,413]'),('id414','[414,414,414]'),('id415','[415,415,415]'),('id416','[416,416,416]'),('id417','[417,417,417]'),('id418','[418,418,418]'),('id419','[419,419,419]'),('id420','[420,420,420]'),('id421','[421,421,421]'),('id422','[422,422,422]'),('id423','[423,423,423]'),('id424','[424,424,424]'),('id425','[425,425,425]'),('id426','[426,426,426]'),('id427','[427,427,427]'),('id428','[428,428,428]'),('id429','[429,429,429]'),('id430','[430,430,430]'),('id431','[431,431,431]'),('id432','[432,432,432]'),('id433','[433,433,433]'),('id434','[434,434,434]'),('id435','[435,435,435]'),('id436','[436,436,436]'),('id437','[437,437,437]'),('id438','[438,438,438]'),('id439','[439,439,439]'),('id440','[440,440,440]'),('id441','[441,441,441]'),('id442','[442,442,442]'),('id443','[443,443,443]'),('id444','[444,444,444]'),('id445','[445,445,445]'),('id446','[446,446,446]'),('id447','[447,447,447]'),('id448','[448,448,448]'),('id449','[449,449,449]'),('id450','[450,450,450]'),('id451','[451,451,451]'),('id452','[452,452,452]'),('id453','[453,453,453]'),('id454','[454,454,454]'),('id455','[455,455,455]'),('id456','[456,456,456]'),('id457','[457,457,457]'),('id458','[458,458,458]'),('id459','[459,459,459]'),('id460','[460,460,460]'),('id461','[461,461,461]'),('id462','[462,462,462]'),('id463','[463,463,463]'),('id464','[464,464,464]'),('id465','[465,465,465]'),('id466','[466,466,466]'),('id467','[467,467,467]'),('id468','[468,468,468]'),('id469','[469,469,469]'),('id470','[470,470,470]'),('id471','[471,471,471]'),('id472','[472,472,472]'),('id473','[473,473,473]'),('id474','[474,474,474]'),('id475','[475,475,475]'),('id476','[476,476,476]'),('id477','[477,477,477]'),('id478','[478,478,478]'),('id479','[479,479,479]'),('id480','[480,480,480]'),('id481','[481,481,481]'),('id482','[482,482,482]'),('id483','[483,483,483]'),('id484','[484,484,484]'),('id485','[485,485,485]'),('id486','[486,486,486]'),('id487','[487,487,487]'),('id488','[488,488,488]'),('id489','[489,489,489]'),('id490','[490,490,490]'),('id491','[491,491,491]'),('id492','[492,492,492]'),('id493','[493,493,493]'),('id494','[494,494,494]'),('id495','[495,495,495]'),('id496','[496,496,496]'),('id497','[497,497,497]'),('id498','[498,498,498]'),('id499','[499,499,499]');
|
|
analyze table t;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
alter table t compact;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // large dataset, fixed vector, non-clustered char PK, indexed
|
|
create table t(id varchar(40) primary key NONCLUSTERED, vec vector(3), vector index ((vec_l2_distance(vec))));
|
|
insert into t values ('id0','[0,0,0]'),('id1','[1,1,1]'),('id2','[2,2,2]'),('id3','[3,3,3]'),('id4','[4,4,4]'),('id5','[5,5,5]'),('id6','[6,6,6]'),('id7','[7,7,7]'),('id8','[8,8,8]'),('id9','[9,9,9]'),('id10','[10,10,10]'),('id11','[11,11,11]'),('id12','[12,12,12]'),('id13','[13,13,13]'),('id14','[14,14,14]'),('id15','[15,15,15]'),('id16','[16,16,16]'),('id17','[17,17,17]'),('id18','[18,18,18]'),('id19','[19,19,19]'),('id20','[20,20,20]'),('id21','[21,21,21]'),('id22','[22,22,22]'),('id23','[23,23,23]'),('id24','[24,24,24]'),('id25','[25,25,25]'),('id26','[26,26,26]'),('id27','[27,27,27]'),('id28','[28,28,28]'),('id29','[29,29,29]'),('id30','[30,30,30]'),('id31','[31,31,31]'),('id32','[32,32,32]'),('id33','[33,33,33]'),('id34','[34,34,34]'),('id35','[35,35,35]'),('id36','[36,36,36]'),('id37','[37,37,37]'),('id38','[38,38,38]'),('id39','[39,39,39]'),('id40','[40,40,40]'),('id41','[41,41,41]'),('id42','[42,42,42]'),('id43','[43,43,43]'),('id44','[44,44,44]'),('id45','[45,45,45]'),('id46','[46,46,46]'),('id47','[47,47,47]'),('id48','[48,48,48]'),('id49','[49,49,49]'),('id50','[50,50,50]'),('id51','[51,51,51]'),('id52','[52,52,52]'),('id53','[53,53,53]'),('id54','[54,54,54]'),('id55','[55,55,55]'),('id56','[56,56,56]'),('id57','[57,57,57]'),('id58','[58,58,58]'),('id59','[59,59,59]'),('id60','[60,60,60]'),('id61','[61,61,61]'),('id62','[62,62,62]'),('id63','[63,63,63]'),('id64','[64,64,64]'),('id65','[65,65,65]'),('id66','[66,66,66]'),('id67','[67,67,67]'),('id68','[68,68,68]'),('id69','[69,69,69]'),('id70','[70,70,70]'),('id71','[71,71,71]'),('id72','[72,72,72]'),('id73','[73,73,73]'),('id74','[74,74,74]'),('id75','[75,75,75]'),('id76','[76,76,76]'),('id77','[77,77,77]'),('id78','[78,78,78]'),('id79','[79,79,79]'),('id80','[80,80,80]'),('id81','[81,81,81]'),('id82','[82,82,82]'),('id83','[83,83,83]'),('id84','[84,84,84]'),('id85','[85,85,85]'),('id86','[86,86,86]'),('id87','[87,87,87]'),('id88','[88,88,88]'),('id89','[89,89,89]'),('id90','[90,90,90]'),('id91','[91,91,91]'),('id92','[92,92,92]'),('id93','[93,93,93]'),('id94','[94,94,94]'),('id95','[95,95,95]'),('id96','[96,96,96]'),('id97','[97,97,97]'),('id98','[98,98,98]'),('id99','[99,99,99]'),('id100','[100,100,100]'),('id101','[101,101,101]'),('id102','[102,102,102]'),('id103','[103,103,103]'),('id104','[104,104,104]'),('id105','[105,105,105]'),('id106','[106,106,106]'),('id107','[107,107,107]'),('id108','[108,108,108]'),('id109','[109,109,109]'),('id110','[110,110,110]'),('id111','[111,111,111]'),('id112','[112,112,112]'),('id113','[113,113,113]'),('id114','[114,114,114]'),('id115','[115,115,115]'),('id116','[116,116,116]'),('id117','[117,117,117]'),('id118','[118,118,118]'),('id119','[119,119,119]'),('id120','[120,120,120]'),('id121','[121,121,121]'),('id122','[122,122,122]'),('id123','[123,123,123]'),('id124','[124,124,124]'),('id125','[125,125,125]'),('id126','[126,126,126]'),('id127','[127,127,127]'),('id128','[128,128,128]'),('id129','[129,129,129]'),('id130','[130,130,130]'),('id131','[131,131,131]'),('id132','[132,132,132]'),('id133','[133,133,133]'),('id134','[134,134,134]'),('id135','[135,135,135]'),('id136','[136,136,136]'),('id137','[137,137,137]'),('id138','[138,138,138]'),('id139','[139,139,139]'),('id140','[140,140,140]'),('id141','[141,141,141]'),('id142','[142,142,142]'),('id143','[143,143,143]'),('id144','[144,144,144]'),('id145','[145,145,145]'),('id146','[146,146,146]'),('id147','[147,147,147]'),('id148','[148,148,148]'),('id149','[149,149,149]'),('id150','[150,150,150]'),('id151','[151,151,151]'),('id152','[152,152,152]'),('id153','[153,153,153]'),('id154','[154,154,154]'),('id155','[155,155,155]'),('id156','[156,156,156]'),('id157','[157,157,157]'),('id158','[158,158,158]'),('id159','[159,159,159]'),('id160','[160,160,160]'),('id161','[161,161,161]'),('id162','[162,162,162]'),('id163','[163,163,163]'),('id164','[164,164,164]'),('id165','[165,165,165]'),('id166','[166,166,166]'),('id167','[167,167,167]'),('id168','[168,168,168]'),('id169','[169,169,169]'),('id170','[170,170,170]'),('id171','[171,171,171]'),('id172','[172,172,172]'),('id173','[173,173,173]'),('id174','[174,174,174]'),('id175','[175,175,175]'),('id176','[176,176,176]'),('id177','[177,177,177]'),('id178','[178,178,178]'),('id179','[179,179,179]'),('id180','[180,180,180]'),('id181','[181,181,181]'),('id182','[182,182,182]'),('id183','[183,183,183]'),('id184','[184,184,184]'),('id185','[185,185,185]'),('id186','[186,186,186]'),('id187','[187,187,187]'),('id188','[188,188,188]'),('id189','[189,189,189]'),('id190','[190,190,190]'),('id191','[191,191,191]'),('id192','[192,192,192]'),('id193','[193,193,193]'),('id194','[194,194,194]'),('id195','[195,195,195]'),('id196','[196,196,196]'),('id197','[197,197,197]'),('id198','[198,198,198]'),('id199','[199,199,199]'),('id200','[200,200,200]'),('id201','[201,201,201]'),('id202','[202,202,202]'),('id203','[203,203,203]'),('id204','[204,204,204]'),('id205','[205,205,205]'),('id206','[206,206,206]'),('id207','[207,207,207]'),('id208','[208,208,208]'),('id209','[209,209,209]'),('id210','[210,210,210]'),('id211','[211,211,211]'),('id212','[212,212,212]'),('id213','[213,213,213]'),('id214','[214,214,214]'),('id215','[215,215,215]'),('id216','[216,216,216]'),('id217','[217,217,217]'),('id218','[218,218,218]'),('id219','[219,219,219]'),('id220','[220,220,220]'),('id221','[221,221,221]'),('id222','[222,222,222]'),('id223','[223,223,223]'),('id224','[224,224,224]'),('id225','[225,225,225]'),('id226','[226,226,226]'),('id227','[227,227,227]'),('id228','[228,228,228]'),('id229','[229,229,229]'),('id230','[230,230,230]'),('id231','[231,231,231]'),('id232','[232,232,232]'),('id233','[233,233,233]'),('id234','[234,234,234]'),('id235','[235,235,235]'),('id236','[236,236,236]'),('id237','[237,237,237]'),('id238','[238,238,238]'),('id239','[239,239,239]'),('id240','[240,240,240]'),('id241','[241,241,241]'),('id242','[242,242,242]'),('id243','[243,243,243]'),('id244','[244,244,244]'),('id245','[245,245,245]'),('id246','[246,246,246]'),('id247','[247,247,247]'),('id248','[248,248,248]'),('id249','[249,249,249]'),('id250','[250,250,250]'),('id251','[251,251,251]'),('id252','[252,252,252]'),('id253','[253,253,253]'),('id254','[254,254,254]'),('id255','[255,255,255]'),('id256','[256,256,256]'),('id257','[257,257,257]'),('id258','[258,258,258]'),('id259','[259,259,259]'),('id260','[260,260,260]'),('id261','[261,261,261]'),('id262','[262,262,262]'),('id263','[263,263,263]'),('id264','[264,264,264]'),('id265','[265,265,265]'),('id266','[266,266,266]'),('id267','[267,267,267]'),('id268','[268,268,268]'),('id269','[269,269,269]'),('id270','[270,270,270]'),('id271','[271,271,271]'),('id272','[272,272,272]'),('id273','[273,273,273]'),('id274','[274,274,274]'),('id275','[275,275,275]'),('id276','[276,276,276]'),('id277','[277,277,277]'),('id278','[278,278,278]'),('id279','[279,279,279]'),('id280','[280,280,280]'),('id281','[281,281,281]'),('id282','[282,282,282]'),('id283','[283,283,283]'),('id284','[284,284,284]'),('id285','[285,285,285]'),('id286','[286,286,286]'),('id287','[287,287,287]'),('id288','[288,288,288]'),('id289','[289,289,289]'),('id290','[290,290,290]'),('id291','[291,291,291]'),('id292','[292,292,292]'),('id293','[293,293,293]'),('id294','[294,294,294]'),('id295','[295,295,295]'),('id296','[296,296,296]'),('id297','[297,297,297]'),('id298','[298,298,298]'),('id299','[299,299,299]'),('id300','[300,300,300]'),('id301','[301,301,301]'),('id302','[302,302,302]'),('id303','[303,303,303]'),('id304','[304,304,304]'),('id305','[305,305,305]'),('id306','[306,306,306]'),('id307','[307,307,307]'),('id308','[308,308,308]'),('id309','[309,309,309]'),('id310','[310,310,310]'),('id311','[311,311,311]'),('id312','[312,312,312]'),('id313','[313,313,313]'),('id314','[314,314,314]'),('id315','[315,315,315]'),('id316','[316,316,316]'),('id317','[317,317,317]'),('id318','[318,318,318]'),('id319','[319,319,319]'),('id320','[320,320,320]'),('id321','[321,321,321]'),('id322','[322,322,322]'),('id323','[323,323,323]'),('id324','[324,324,324]'),('id325','[325,325,325]'),('id326','[326,326,326]'),('id327','[327,327,327]'),('id328','[328,328,328]'),('id329','[329,329,329]'),('id330','[330,330,330]'),('id331','[331,331,331]'),('id332','[332,332,332]'),('id333','[333,333,333]'),('id334','[334,334,334]'),('id335','[335,335,335]'),('id336','[336,336,336]'),('id337','[337,337,337]'),('id338','[338,338,338]'),('id339','[339,339,339]'),('id340','[340,340,340]'),('id341','[341,341,341]'),('id342','[342,342,342]'),('id343','[343,343,343]'),('id344','[344,344,344]'),('id345','[345,345,345]'),('id346','[346,346,346]'),('id347','[347,347,347]'),('id348','[348,348,348]'),('id349','[349,349,349]'),('id350','[350,350,350]'),('id351','[351,351,351]'),('id352','[352,352,352]'),('id353','[353,353,353]'),('id354','[354,354,354]'),('id355','[355,355,355]'),('id356','[356,356,356]'),('id357','[357,357,357]'),('id358','[358,358,358]'),('id359','[359,359,359]'),('id360','[360,360,360]'),('id361','[361,361,361]'),('id362','[362,362,362]'),('id363','[363,363,363]'),('id364','[364,364,364]'),('id365','[365,365,365]'),('id366','[366,366,366]'),('id367','[367,367,367]'),('id368','[368,368,368]'),('id369','[369,369,369]'),('id370','[370,370,370]'),('id371','[371,371,371]'),('id372','[372,372,372]'),('id373','[373,373,373]'),('id374','[374,374,374]'),('id375','[375,375,375]'),('id376','[376,376,376]'),('id377','[377,377,377]'),('id378','[378,378,378]'),('id379','[379,379,379]'),('id380','[380,380,380]'),('id381','[381,381,381]'),('id382','[382,382,382]'),('id383','[383,383,383]'),('id384','[384,384,384]'),('id385','[385,385,385]'),('id386','[386,386,386]'),('id387','[387,387,387]'),('id388','[388,388,388]'),('id389','[389,389,389]'),('id390','[390,390,390]'),('id391','[391,391,391]'),('id392','[392,392,392]'),('id393','[393,393,393]'),('id394','[394,394,394]'),('id395','[395,395,395]'),('id396','[396,396,396]'),('id397','[397,397,397]'),('id398','[398,398,398]'),('id399','[399,399,399]'),('id400','[400,400,400]'),('id401','[401,401,401]'),('id402','[402,402,402]'),('id403','[403,403,403]'),('id404','[404,404,404]'),('id405','[405,405,405]'),('id406','[406,406,406]'),('id407','[407,407,407]'),('id408','[408,408,408]'),('id409','[409,409,409]'),('id410','[410,410,410]'),('id411','[411,411,411]'),('id412','[412,412,412]'),('id413','[413,413,413]'),('id414','[414,414,414]'),('id415','[415,415,415]'),('id416','[416,416,416]'),('id417','[417,417,417]'),('id418','[418,418,418]'),('id419','[419,419,419]'),('id420','[420,420,420]'),('id421','[421,421,421]'),('id422','[422,422,422]'),('id423','[423,423,423]'),('id424','[424,424,424]'),('id425','[425,425,425]'),('id426','[426,426,426]'),('id427','[427,427,427]'),('id428','[428,428,428]'),('id429','[429,429,429]'),('id430','[430,430,430]'),('id431','[431,431,431]'),('id432','[432,432,432]'),('id433','[433,433,433]'),('id434','[434,434,434]'),('id435','[435,435,435]'),('id436','[436,436,436]'),('id437','[437,437,437]'),('id438','[438,438,438]'),('id439','[439,439,439]'),('id440','[440,440,440]'),('id441','[441,441,441]'),('id442','[442,442,442]'),('id443','[443,443,443]'),('id444','[444,444,444]'),('id445','[445,445,445]'),('id446','[446,446,446]'),('id447','[447,447,447]'),('id448','[448,448,448]'),('id449','[449,449,449]'),('id450','[450,450,450]'),('id451','[451,451,451]'),('id452','[452,452,452]'),('id453','[453,453,453]'),('id454','[454,454,454]'),('id455','[455,455,455]'),('id456','[456,456,456]'),('id457','[457,457,457]'),('id458','[458,458,458]'),('id459','[459,459,459]'),('id460','[460,460,460]'),('id461','[461,461,461]'),('id462','[462,462,462]'),('id463','[463,463,463]'),('id464','[464,464,464]'),('id465','[465,465,465]'),('id466','[466,466,466]'),('id467','[467,467,467]'),('id468','[468,468,468]'),('id469','[469,469,469]'),('id470','[470,470,470]'),('id471','[471,471,471]'),('id472','[472,472,472]'),('id473','[473,473,473]'),('id474','[474,474,474]'),('id475','[475,475,475]'),('id476','[476,476,476]'),('id477','[477,477,477]'),('id478','[478,478,478]'),('id479','[479,479,479]'),('id480','[480,480,480]'),('id481','[481,481,481]'),('id482','[482,482,482]'),('id483','[483,483,483]'),('id484','[484,484,484]'),('id485','[485,485,485]'),('id486','[486,486,486]'),('id487','[487,487,487]'),('id488','[488,488,488]'),('id489','[489,489,489]'),('id490','[490,490,490]'),('id491','[491,491,491]'),('id492','[492,492,492]'),('id493','[493,493,493]'),('id494','[494,494,494]'),('id495','[495,495,495]'),('id496','[496,496,496]'),('id497','[497,497,497]'),('id498','[498,498,498]'),('id499','[499,499,499]');
|
|
analyze table t;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tiflash";
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
alter table t compact;
|
|
select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select * from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select id from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
explain select vec from t order by vec_l2_distance(vec, '[200.8,200.8,200.8]') limit 2;
|
|
select sleep(5);
|
|
set @@tidb_isolation_read_engines="tidb,tikv,tiflash";
|
|
drop table t;
|
|
|
|
--echo // Test cases from expression/integration_test
|
|
use test;
|
|
drop table if exists t;
|
|
create table t(embedding VECTOR);
|
|
insert into t values ('[1,2,3]');
|
|
insert into t values ('[1,2,3,4]');
|
|
-- error 1105
|
|
alter table t modify column embedding VECTOR(3);
|
|
delete from t where vec_dims(embedding) != 3;
|
|
alter table t modify column embedding VECTOR(3);
|
|
-- error 1105
|
|
insert into t values ('[]');
|
|
-- error 1105
|
|
insert into t values ('[1,2,3,4]');
|
|
-- error 1105
|
|
insert into t values (VEC_FROM_TEXT('[]'));
|
|
-- error 1105
|
|
insert into t values (VEC_FROM_TEXT('[1,2,3,4]'));
|
|
-- error 1105
|
|
update t set embedding = '[1,2,3,4]' where embedding = '[1,2,3]';
|
|
-- error 1105
|
|
update t set embedding = '[]' where embedding = '[1,2,3]';
|
|
alter table t modify column embedding VECTOR;
|
|
insert into t values ('[1,2,3,4]');
|
|
-- error 1105
|
|
alter table t modify column embedding VECTOR(16384); |