INDEX on / seek in MARIA DB

[mauri.menabue]

Hi All
how do you create an index with multiple fields in Maria Db and then also the seek command

with DBF Es. Index ON COD_CLI + STR(COD_NUM,2) ... with MARIA DB ..........................
CLIENT->(dbseek(COD_CLI+str(COD_NUM,2))) with MARIA DB oRS:seek(COD_CLI, COD_NUM) correct ?

tia

[nageswaragunupudi]

In RDBMSs other than DBFs, indexes are not used for Navigation or for Seeks.
The purpose of indexes is only to optimize where clauses for queries.
Intelligent planning of indexes is an essential part of database design to optimize queries in OLAP (Online Analytical processing).
For the size of data tables most of use, no indexes are ever necessary. We need to think about them when we are dealing with multi-million records tables.

Now, as for Seek.
If you are using FWMariaDB for MySql or MariaDB, this works with single field.
Note: No indexes need to be build. Creating indexes is an unnecessary overhead.

Code: Select all  Expand view  RUN

oRs:SetOrder( "fieldname" )
oRs:Seek( uValue )
 

OR

Code: Select all  Expand view  RUN

oRs:SetOrder( "CITY ASC,SALARY DESC" )  // multicol sort
oRs:Seek( cValue ) // works only for first field. i.e., CITY
 

Now, your requirement is to seek on multi-field values.
We advise you to use oRs:Locate()

Code: Select all  Expand view  RUN

oRs:Locate( "CITY = 'N' .AND. AGE > 40" )
// for locating next
oRs:Locate( "CITY = 'N' .AND. AGE > 40",, .T. )
 

Note: With FWMariaDB, all sorting, seeking and locating are done in memory. Database is not acccessed or queried. Therefore these operations are very fast.

[NWKL]

Mr Nages, where i can find one guide to migrate ads files to fwmaria, can i use the same syntax ?
i use ads and tdatabase, can i only convert and open files using fwmaria and use the code with syntax or i have to change all? i can use an local and server (embeded) like i use with ads?

thanks
best regards

[shrifw]

Hi ,

This can achieve using GENRATED COLUMNS in Maria DB. as given code below. The Given code is just an idea you have to modify the code to remove syntax errors.

Steps
1. Add Generated Column
2. Create Index on Generated Column

Code: Select all  Expand view  RUN

CREATE TABLE Employees (
  Id INTEGER PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  FullName VARCHAR(101) AS (CONCAT(FirstName, ' ', LastName))
);


ALTER TABLE Employees
  ADD FullName VARCHAR(101) GENERATED ALWAYS AS (CONCAT(FirstName, ' ', LastName)) STORED;
 
CREATE INDEX `names_idx` ON `Employees`(`FullName`);  


 

Thanks
Shridhar

[nageswaragunupudi]

Mr Nages, where i can find one guide to migrate ads files to fwmaria, can i use the same syntax ?
i use ads and tdatabase, can i only convert and open files using fwmaria and use the code with syntax or i have to change all? i can use an local and server (embeded) like i use with ads?

thanks
best regards

First, please see
viewtopic.php?f=3&t=33286
for complete documentation of FWMariadb/MySql

It is very easy to import DBF files to MySql server using this library.

You can not totally use the same syntax for the entire application. You will need to make some changes.
Because you are now using TDatabase, you will find the new FWMariaRowSet class ( class to handle MySql tables ) very similar to TDatabase class. So changes should be less.

If you start. we will provide you the best possible support on the forums.

[nageswaragunupudi]

Hi All
how do you create an index with multiple fields in Maria Db and then also the seek command

with DBF Es. Index ON COD_CLI + STR(COD_NUM,2) ... with MARIA DB ..........................
CLIENT->(dbseek(COD_CLI+str(COD_NUM,2))) with MARIA DB oRS:seek(COD_CLI, COD_NUM) correct ?

tia

We are thinking of sorting and seek on compound expressions.
We will announce this enhancement soon