Full-Text search is slow

• sconci

  Newbie

  Points: 5

  More actions

  December 15, 2014 at 5:40 pm

  #301885

  I am having a hard time trying to figure out what's wrong with my query, which is not fast enough.

  This is it:

  SELECT

  j.JobId ,

  j.Title ,

  j.GeoZipcodeId,

  j.Zipcode ,

  ct.Rank

  FROM Job J

  JOIN CONTAINSTABLE( job , Title , '"Analyst"' )ct

  ON ct.[Key] = j.JobId

  WHERE j.IsActive = 1

  AND j.GeoCityId = 16713

  It's a big table, I admit; It has over 11 million records.

  Table structure:

  CREATE TABLE [Job](

  [JobId] [int] IDENTITY(1,1) NOT NULL,

  [Title] [varchar](500) NOT NULL,

  [GeoCityId] [int] NULL,

  [GeoZipcodeId] [int] NULL,

  [Zipcode] [char](5) NOT NULL,

  [IsActive] [bit] NOT NULL DEFAULT ((1)),

  CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED

  (

  [JobId] ASC

  )

  )

  CREATE FULLTEXT INDEX ON Job(Title Language 1033) KEY INDEX PK_Job

  WITH STOPLIST = SYSTEM, CHANGE_TRACKING AUTO

  No other indexes are created, and this is the execution plan:

  

  Weird thing to me is that the Analyzer is not giving me any Index recommendation, which is weird cause it usually provides it.

  Is the clustered index (PK) the problem? or maybe I'm missing and index creation?

  I'm pretty sure is:

  CREATE NONCLUSTERED INDEX UX_Job_City_Active

  ON [dbo].[Job] ([GeoCityId],[IsActive])

  But I wanted to be sure that the full-text index has nothing to do with it before creating the index (which I assume is correct but if not, then I would have wasted a lot of time).

  Thanks in advance!