Mysql table indexes

[vilian]

Hi Guys,
How could I know the indexes of a mysql table has ?

[FranciscoA]

Try this way:

Code: Select all  Expand view

oVer := oServer:Query("SHOW INDEX FROM " + cTable + ";")

Regards.

[FranciscoA]

Otra manera:

Code: Select all  Expand view

//Para una tabla
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'retalcal';

//Para todas las tablas de una BDD
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'fapsoftware';

Saludos.

[vilian]

Thank you

[nageswaragunupudi]

Hi Guys,
How could I know the indexes of a mysql table has ?

Code: Select all  Expand view

aIndexes := oCn:ListIndexes( cTable )
 

[vilian]

Thank you

[Horizon]

Hi Guys,
How could I know the indexes of a mysql table has ?

Code: Select all  Expand view

aIndexes := oCn:ListIndexes( cTable )
 

Hi Mr. Rao,
Is there any function to create index?

[nageswaragunupudi]

Is there any function to create index?

No.

Not exactly but this function automatically creates a primary key index:

Code: Select all  Expand view

METHOD MakePrimaryKey( cTable, cCol )

[Horizon]

Is there any function to create index?

No.

Not exactly but this function automatically creates a primary key index:

Code: Select all  Expand view

METHOD MakePrimaryKey( cTable, cCol )

Hi Mr. Rao,

Do you mean we don't need index other than a primary key? Wouldn't it be better to index the connection variables of detail tables and master table?

[nageswaragunupudi]

Do you mean we don't need index other than a primary key?

a) We need to create unique indexes for enforcing unique constraint for a column or set of columns. If we want to create unique constraint to a single column, we can either create while creating the table or later create a unique index by:

Code: Select all  Expand view

oCn:Execute( "CREATE UNIQUE INDEX cust_name_uidx ON cust( name )" )
 

b) In large tables we may need to create indexes on some columns to optimize queries. It is normally better to keep such indexes to minimum or none. Note: Optimizing queries is a subject by itself.

Code: Select all  Expand view

oCn:Execute( "CREATE INDEX ON employee_sex_idx ON employee( _ )" )
 

Wouldn't it be better to index the connection variables of detail tables and master table?

We do it by creating foreign key relationships. Our library provides special features to deal with parent-child relationships and you can find samples in the samples folder as well in the forum if you search.