How many INDEX can create ON MySql and How to use it?

[dutch]

Dear All,

1. How many index on MySql (as index TAG on ADS index .CDX)?
2. How to use it like ORDSETFOCUS()?
3. How to RecordSet()::ReQuery?

Thanks in advance for any help.
Dutch

[AHF]

Ducth,

Are you using adordd?

[dutch]

Dear Antonio,

I'm testing TMySql and TDolphin too for more solution. Because I'm new with SQL Database and need to know many solution for migration. ADORDD is great too but I need to learn more and more.

Thanks for your great support.

Ducth,

Are you using adordd?

[carlos vargas]

user not control what index using engine sql to return a query.
only in extreme situaution is posible.

each engine evaluate a query and using the index more adecuate.

please create index only for fields using in where statement.
for example:
select user code, name from user where num_usr='1' limit 1
here the column num_usr must index type unique.
for acelerate the select.

i each table in my databasea have a int column not negative not null call 'my_recno', this column is autoincremental and is defined primary key.
this fiels is using for engine for indentify each row for update an delete statement.

sorry for my bad english.

salu2
carlos vargas

[Rick Lipkin]

Dutch

I rarely use indexes, however, there is a place and use for LIMITED use of indexes .. see bold text below. Using Sql indexes is MUCH different than in .dbf .. and I would ONLY recommend you use indexes for the relational fields.

Please do not confuse 'Primary Key' with an index .. a Primary key is a unique row identifier as Carlos mentions and (generally speaking) has nothing to do with indexing. Every Sql Table should have a 'Primary Key'.

Rick Lipkin

http://www.w3schools.com/sql/sql_create_index.asp

SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole table.
Indexes

An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)
SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the syntax for creating indexes in your database.
CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the "Persons" table:
CREATE INDEX PIndex
ON Persons (LastName)

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:
CREATE INDEX PIndex
ON Persons (LastName, FirstName)

[dutch]

Dear Rick,

Thank you so much but I have some question.
1.) If I need to use RELATION, I can use INDEX. Can I use Primary Key for relation?
2.) If I need to do as TCOMBOBOX:lIncSearch option. Can I Requery from the :RecordSet? Or I have to Query:Excute() from table everytime?
3.) I will use RECNO field name for AUTO_INCREMENT. Does it must be Primary Key?

Thanks in advance.

[Rick Lipkin]

Dutch

There is no right or wrong way to work with SQL. If you feel comfortable using AdoRdd and .dbf syntax .. you can do that. If you want to use the Microsoft ADO Class and methods you can do that as well or the FW_xx wrappers that use the ADO Class in easy to use functions or you can do it the manual way with TOleAuto.

https://msdn.microsoft.com/en-us/librar ... 85%29.aspx

If you use AdoRdd I am assuming you can use a 'set relation to' ? ( I just do not know ) or you can craft your Sql statements to select fields from one table and from another table on a common field with a simple join without the use of indexes.

Ms Access has a very simple query builder where you can pick and chose your fields from tables and create a join to make a relational recordset.




Here is a simple join on Customer Id that creates a join ( relation ) between two tables

SELECT
Customer.[First Name],
Customer.[Last Name],
Customer.Address1,
Customer.City,
Customer.State,
Payments.[Payment Method],
Payments.[Payment Amount],
Payments.[Date Collected],
Payments.[Invoice Number]
FROM Customer LEFT JOIN Payments ON Customer.[Customer Id] = Payments.[Customer Id]

When I use a ComboBox .. I generate my Recordset and then based on the field on the recordset I create the Combobox array like this :

Code: Select all  Expand view

cSql := "Select * From [Inventory Location] order by Location"

oRsLoc := TOleAuto():New( "ADODB.Recordset" )
oRsLoc:CursorType     := 1        // opendkeyset
oRsLoc:CursorLocation := 3        // local cache
oRsLoc:LockType       := 3        // lockoportunistic

TRY
   oRsLoc:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening INVENTORY LOCATION table" )
   oRsInv:Delete()
   oRsInv:CLose()
   oRsInv := NIL
   oRsInvDetail:CLose()
   oRsInvDetail := NIL
   oRsEmp:CLose()
   RETURN(.F.)
END TRY

// this is used in the inventory table to define location
cLoc := substr("Main"+space(25),1,25)
aLoc := {}

If oRsLoc:eof
   oRsEmp:CLose()
   oRsInv:Delete()
   oRsInv:CLose()
   oRsInv := NIL
   oRsInvDetail:Close()
   oRsLoc:Close()
   oRsLoc := NIL
   Saying := "Sorry .. there are no Defined Inventory Locations"+chr(10)
   Saying += "Please seek your Administrator"+chr(10)
   MsgInfo( Saying )
   Return(.f.)
Else
   oRsLoc:MoveFirst()
   Do While .not. oRsLoc:eof

      cName := substr(oRsLoc:Fields("Location"):Value,1,25)
      AAdd( aLoc, cName )

      oRsLoc:MoveNext()

   End Do
Endif
oRsLoc:Close()

...

REDEFINE COMBOBOX oLoc   var cLoc     ID 131 of oWorkB  ;
                ITEMS aLoc UPDATE
oLoc:lIncSearch = .T.
 

RECNO would be a good unique row Primary Key and AUTO_INCREMENT would be just fine.

Rick Lipkin

[AHF]

Ducth, Rick,

You can use adordd together with all of those options (TDatabase classes, adofuncs etc).
The biggest advantages of working with adordd is:

1) You dont change the way you are used to work and you dont loose any of the SQL features and performance.

2) You have all information of each recordset encapsulated on a workarea where you can query all workarea information through any workarea functions.
You dont have to save the recordset pass it to other function in order to access it.
You simply select the corresponding workarea and you will get it.

3) You can always get the workarea recordset by calling hb_adoRddGetRecordSet( nWorkArea ) and use it for your own needs. But still all workarea information will continue to be there to you.

4) Indexes in adordd are nothing more than predefined ORDER BY clause that are automatically created with usual index functions like any other rdd.
Creation of physical index files should only be done by the DBA managing the database otherwise you might get very bad results.

5) Any rdd function like SET RELATION are 100% supported biu you can do it like Rick.

6) You can port immediately a app to any SQL and then within you own time youll aadd other functionalities.

Resuming you can and should use all other tools available like those mentioned by Rick.

Ex

Code: Select all  Expand view

hb_GetAdoConnection():Execute(  CREATE VIEW MYVIEW WITH A RELATION )
SELE 0
USE MYVIEW ALIAS MYVIEW

// OTHER FUNCTION
hb_adoRddGetRecordSet( SELECT( "MYVIEW") ):CLOSE()
hb_adoRddGetRecordSet( SELECT( "MYVIEW") ):OPEN( .... another query

// OTHER FUNCTION
MYVIEW->(DBSEEK( XXX))

// OTHER FUNCTION
oRs := hb_adoRddGetRecordSet( SELECT( "MYVIEW") )
oRs := FW_AdoPivotRS( hb_GetAdoConnection(), "MYVIEW", cRowFld, cColFld, cValFld, cAggrFunc )
SELECT MYVIEW
BROWSE()
 

[dutch]

Dear Rick, Antonio

Thank you for your kindness help.