FULLTEXT index creation slow

Help
1

I have the next table

CREATE TABLE Pendings
(
id BIGINT UNSIGNED AUTO_INCREMENT
, ProcessId BIGINT
, PendingId BIGINT
, Path TEXT
, Name TEXT
, CreationDate TEXT
, TaskName TEXT
, PendingCategoryId INT
, UserFullName TEXT
, DecisionDisplayName TEXT
, Color TEXT
, Sufix TEXT
, Seed Text
, Tags TEXT CHARACTER SET latin1 COLLATE latin1_spanish_ci
, TagsValues TEXT
, ProcessKey TEXT
, Image TEXT
, Area TEXT
, KEY (id) USING CLUSTERED COLUMNSTORE
, FULLTEXT(Name,TaskName,UserFullName,DecisionDisplayName,Color,Sufix,Seed,TagsValues,Area)
)

but after insert i try to realize this query

select DISTINCT
           Path
         , p.ProcessId
         , p.PendingId
         , Name
         , CreationDate
         , TaskName
         , PendingCategoryId
         , UserFullName
         , DecisionDisplayName
         , Color
         , Sufix
         , Seed
         , Tags
         , TagsValues
         , ProcessKey
         , AssignedUserId
         , Image
         , Area
from
           Pendings as p
where
           PendingCategoryId = @
           AND match (Name,TaskName,UserFullName,DecisionDisplayName,Color,Sufix,Seed,TagsValues,Area) against (@)
order by
           CreationDate desc limit @ offset

but the MATCH filter not work even after 40 minutes, how i can reduce this time, i can change some variables of memsql for this?

thanks for your help

2

memSQL will only build the fulltext index when it is converted to on-disk column-oriented format (when a row is just inserted it is in row-oriented format).
You can do “Optimize table Pendings flush” to force all rows convert to columnar format and ensure the index in built.

1 Like