-
[Answers] Indexing
Indexing on a logical is not allowed for performance reasons.
if you really need to build the index you can convert the logical to a string, but we don't recommend it unless there's really no other way around it.
Q:
LARGE table (over 4 millons records and over 1.5gb) with indexes.How do i replace te SET KEY TO xxx to get a similar performance? (and to get full index optimization)
A:
You can use SET FILTER, or you may find Virtual Tables to be helpful
Lianja will optimize on indexes when it can so in your case you just need to have an index on "country" and it will use that and not scan the whole table.
You can experiment using the "explain" command to see how it will perform the query.
So...
index on country tag country
Then...
explain select * from tablename where country="USA"
Also worth noting is that Lianja will also use filtered indexes where possible.
Q:
In vfp you would define an index of type candidate.
A:
Just use the INDEX ON command and the UNIQUE keyword, e.g. in the Console:
open database southwind
use employees
index on trim(lastname)+" "+firstname unique tag fullname
Or in SQL, specify the UNIQUE keyword in the CREATE INDEX command, e.g. in the Console:
create unique index sqlfullname on employees (trim(lastname)+" "+firstname)
You can index on logical fields - they just need to be converted to character first - use LTOS().
Q:
How I can create a normal or production index for deleted records of lianja table?
A:
Indexing on a logical is not allowed for performance reasons.
index on etos(deleted()) to adu_del
All topics in [Answers] alphabetically: http://www.lianja.com/community/show...ll=1#post12352
-
Q:
The last line below gives 'Data type mismatch' error. I have also tried '(RID_Exe+MachineName)' as per the docs but then I get 'Expression was expected' error.
Code:
create table SWMEXEMACHINES
alter table SWMEXEMACHINES add column RID_Exe numeric(18,0) not null
alter table SWMEXEMACHINES add column MachineName char(30) not null
alter table SWMEXEMACHINES add column Enabled bit null
alter table SWMEXEMACHINES add primary key (RID_Exe,MachineName) tag pk_SWMEXEMACHINES
A:
You are trying to create an index on mixed data types. You need to make sure the expression results in a fixed length character string. You cannot concatenate a string to a numeric value in Lianja.
Code:
alter table SWMEXEMACHINES add primary key (str(RID_Exe),MachineName) tag pk_SWMEXEMACHINES
The indexes are much more functional in Lianja than other SQL databases as they can be created on very complex expressions.
Q:
I imported a VFP DBC into Lianja. Where do I configure the Indexes? I don't see where do I set them.
A:
After you imported your VFP dbc the indexes that you had are recreated in Lianja format.
You can open the database with OPEN DATABASE in the console, open a table with USE, then use LIST STATUS to view the indexes.
You can create new indexes using the VFP-style INDEX ON command or the SQL CREATE INDEX command.
Q:
Would you say that I don't need to create indexes in new tables I create in Lianja? Do LIanja needs the indexes to be created or Lianja creates them as needed?
A:
In most cases Lianja will build the indexes as you develop your app visually. There are cases however (e.g for SQL optimization) where you need to create indexes manually.
Q:
I cannot get this working. As far as I can tell FK's need to work on indexes (where I am accustomed to adding them to columns). This example may look awkward but it shows what I want to achieve:
Code:
CREATE TABLE supplier (SuppId i PRIMARY KEY, SuppRef c(10) UNIQUE, SuppName c(40) UNIQUE)
CREATE TABLE purchase_order (POId i PRIMARY KEY, PO_SuppRef c(10), POtotal n(10,2))
CREATE INDEX sup_idx1 ON Supplier (SuppRef)
CREATE INDEX po_idx1 ON purchase_order (PO_SuppRef)
ALTER TABLE purchase_order ADD FOREIGN KEY po_idx1 TAG po_fk1 REFERENCES supplier TAG sup_idx1
But this gives an error about 'TAG' being an unrecognized phrase. Please help with the code to join
purchase_order.PO_SuppRef -> supplier.SuppRef
A:
Try this:
Code:
CREATE TABLE supplier;
(SuppId int PRIMARY KEY, SuppRef char(10) UNIQUE, SuppName char(40) UNIQUE)
CREATE TABLE purchase_order;
(POId int PRIMARY KEY,;
PO_SuppRef char(10) FOREIGN KEY REFERENCES supplier TAG SuppRef,;
POtotal num(10,2))
Note: UNIQUE creates a Tag index with the same name as the field.
Also, using in-built Lianja UI functionality such as the Relationship Builder, Instant Search, Instant Selections and Grid sorting indexes are generated and selected automatically.
The index is only created by
Code:
ALTER TABLE purchase_order
Code:
ADD FOREIGN KEY (po_suppref) [TAG fk1]
or when you specify the column FOREIGN KEY constraint when creating a column.
The persistent relationship on a particular column is handled by FOREIGN KEY .... REFERENCES or ALTER COLUMN ... REFERENCES and does not create an index as an index is not required in the column's table. An index in the referenced table is required and must be specified in the REFERENCES clause.
All topics in [Answers] alphabetically: http://www.lianja.com/community/show...p?2717-Answers
