![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 8 of 8 total |
![]() |
Thu, Mar 20 2014 11:51 AM | Permanent Link |
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? |
Fri, Mar 21 2014 6:25 PM | Permanent Link |
Barry | 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 |
Mon, Mar 24 2014 9:18 AM | Permanent Link |
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?
|
Mon, Mar 24 2014 10:46 AM | Permanent Link |
Raul ![]() | 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 |
Mon, Mar 24 2014 11:23 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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%' |
Mon, Mar 24 2014 1:49 PM | Permanent Link |
AQC | 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%' |
Mon, Mar 24 2014 5:58 PM | Permanent Link |
Barry | 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 |
Tue, Mar 25 2014 4:40 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |