Indexing Issue

[krisfromohio]

Hello,

I'm having some serious performance issues with a database, and I can't figure out what's wrong with my indexing. Do texis indicies not handle !/not logic well? This is a really small table, with less that 10,000 records, but I am still getting query times over 30 seconds.

Can you please provide some guidance on an indexing strategy that might work?

Thanks!

Kris

(Lots of detals below)

I have a table (jobsearch) with fields as follows:


NAME TYPE LENGTH
------------+------------+------------+
id counter 8
employerId counter 8
lastMod date 4
jobText varchar 3000
title varchar 100
url varchar 50
location varchar 25
company varchar 25


I have the following indicies:

NAME: jobsearch_url
TBNAME: jobsearch
FNAME: jobsearch_url
TYPE: B
FIELDS: url

NAME: jobsearch_jobTextTitle
TBNAME: jobsearch
FNAME: jobsearch_jobTextTitle
TYPE: M
FIELDS: title\jobText

NAME: js_search
TBNAME: jobsearch
FNAME: js_search
TYPE: F
FIELDS: ,,title\jobText location title

NAME: jobsearchUnique
TBNAME: jobsearch
FNAME: jobsearchUnique
TYPE: B
FIELDS: ,,,title location company url

NAME: jobsearch_employerId
TBNAME: jobsearch
FNAME: jobsearch_employerId
TYPE: B
FIELDS: employerId

NAME: jobsearch_location_title
TBNAME: jobsearch
FNAME: jobsearch_location_title
TYPE: B
FIELDS: ,location title


I want to run a query of the form:

SELECT ... FROM jobsearch where location != '%NO-VALUE%', title != '%NO-VALUE%', title\jobText likep 'Some keywords here' and employerId != '44defd32' and employerId != '3888b8c8' and employerId != '394ed99a'

I have found that putting in the 'employerId != ...' mess is faster then doing a join on my employerSites table (where employerID comes from), which is why I'm doing that.

Here's some other miscellaneous information that you might find handy:

[ktraenkn]$ tsql -q "select count(*) from jobsearch"
7215
[ktraenkn]$ tsql -q "select count(*) from jobsearch where location != '%NO-VALUE%' and title != '%NO-VALUE%'"
5520
[ktraenkn]$ tsql -q "select avg(length(jobText)) from jobsearch"
13656

[John]

For a pure != search an index will not be used, although in combination with the likep it should be used. I would include employerid in the compound index, and put the likep clause first, e.g.

where title\jobText likep 'keywords' and (location != '' and title != '' and employerid != '' and ...)