I want to know if I must also create a TEXT INDEX on a column that already have a CONSTRAINT UNIQUE on it if this column is JOIN and/or search intensive?

Must I do the same with a column with CONSTRAINT PRIMARY KEY on it?

AQC,

>I want to know if I must also create a TEXT INDEX on a column that already have a CONSTRAINT UNIQUE on it if this column is JOIN and/or search intensive?

Must I do the same with a column with CONSTRAINT PRIMARY KEY on it?<

In EDB a "Text Index" refers to a full text index. I think you meant to say ".. if I must also create an Index on a VarChar or Char column ...".

The answer would be (probably) no. You can test it yourself in EDBMgr and run your query with  "Request Execution Plan" active. After the query has run, it will display "Hints" to suggest if an index is needed.

I say "probably no" because I am assuming your joins are using a single column. If your table joins are based on 2 or more columns then creating a compound index and using it properly in the join like
select * from table1 t1 left join table2 t2 on
(t1.col1, t1.col2) = (t2.col1, t2.col2)

will make for faster joins if there is a compound index on both tables for (col1, col2) than if you had only an index on col1.

Barry

Just to be sure I underastand, if I have a VarChar column that is a primary key or that is unique in ElevateDb and that is used in a single column join, I don't need to make a text index to make it faster?

On 3/20/2014 11:51 AM, AQC wrote:
> I want to know if I must also create a TEXT INDEX on a column that already have a CONSTRAINT UNIQUE on it if this column is JOIN and/or search intensive?
>
> Must I do the same with a column with CONSTRAINT PRIMARY KEY on it?
>

Not sure you require a TEXT INDEX for joins to begin with - i think text
index is used for CONTAINS type searches.

Raul

AQC

>Just to be sure I underastand, if I have a VarChar column that is a primary key or that is unique in ElevateDb and that is used in a single column join, I don't need to make a text index to make it faster?

That's right.

As Raul says the purpose of the full text index is for use in CONTAINS selections, and if you try and use it for a join you'll get an error

eg

SELECT
_id,_nAME,
_Surname,
_Forename
FROM Companies C
JOIN Contacts W ON W._Skills CONTAINS 'DirectSales'

ElevateDB Error #700 An error was found in the statement at line 6 and column 30 (Invalid expression CONTAINS found, CONTAINS and DOES NOT CONTAIN operators not allowed)

BUT  if you want to use CONTAINS as below

SELECT
_id,_nAME,
_Surname,
_Forename
FROM Companies C
JOIN Career X ON X._fkCompanies = C._ID
JOIN Contacts W ON W._ID = X._fkContacts
WHERE W._Skills CONTAINS 'DirectSales'

then a full text index is a very good idea.

Roy Lambert

ps if you wanted to do something silly like the first example you can always use LIKE eg

SELECT
_id,_nAME,
_Surname,
_Forename
FROM Companies C
JOIN Contacts W ON W._Skills LIKE '%DirectSales%'

OK so the full text indexes will only work with CONTAINS family of keywords.

But what about JOIN queries? If I use the same field with a JOIN clause, my full text is useless and I should create another index on this column or not?

Thanks!

Roy Lambert wrote:

AQC

>Just to be sure I underastand, if I have a VarChar column that is a primary key or that is unique in ElevateDb and that is used in a single column join, I don't need to make a text index to make it faster?

That's right.

As Raul says the purpose of the full text index is for use in CONTAINS selections, and if you try and use it for a join you'll get an error

eg

SELECT
_id,_nAME,
_Surname,
_Forename
FROM Companies C
JOIN Contacts W ON W._Skills CONTAINS 'DirectSales'

ElevateDB Error #700 An error was found in the statement at line 6 and column 30 (Invalid expression CONTAINS found, CONTAINS and DOES NOT CONTAIN operators not allowed)

BUT  if you want to use CONTAINS as below

SELECT
_id,_nAME,
_Surname,
_Forename
FROM Companies C
JOIN Career X ON X._fkCompanies = C._ID
JOIN Contacts W ON W._ID = X._fkContacts
WHERE W._Skills CONTAINS 'DirectSales'

then a full text index is a very good idea.

Roy Lambert

ps if you wanted to do something silly like the first example you can always use LIKE eg

SELECT
_id,_nAME,
_Surname,
_Forename
FROM Companies C
JOIN Contacts W ON W._Skills LIKE '%DirectSales%'

AQC wrote:

>OK so the full text indexes will only work with CONTAINS family of keywords.

But what about JOIN queries? If I use the same field with a JOIN clause, my full text is useless and I should create another index on this column or not?<

Correct, a primary key works fine for table joins because you normally want an exact value match between tables. Of course Unique and non-Unique indexes can also be used in table joins but I would expect faster joins with a primary index.

Example: "Select City.*, State.State_Name from City left join State on State.State=City.State"

A full text index is usually used on a large VarChar, Char, or Memo columns that have *many words* or paragraphs of text in *each row*.  For example, you can create a full text index a Comments column or a Description column (like the fish description of the Delphi Fish Facts demo).

You would never create a full text index on a Char or VarChar column with only one word in it. You only use a full text index on a column with a lot of words in it, like a Memo. Full Text indexes cannot be used for joins and as others mentioned, can only be used with "Contains" .

Barry

AQC


Pretty much as Barry says.

The other thing to make you aware of is the help that EDBManager can give you.

If you use EDBManager to test out your queries then tick "Request Execution Plan". This will generate a report which can be used to identify areas to speed up the query (eg missing indices).

Also, if your query doesn't include a JOIN then tick "Request Sensitive Result Set" (or set the property on the query component). This means that a temporary table does not have to be written and will, generally, speed up your query.

Roy Lambert