Results 1 to 2 of 2

Thread: [Answers] Indexing

  1. #1
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,135

    [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
    Last edited by josipradnik; 2016-11-15 at 03:43.

  2. #2
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,135
    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

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us