topic-38920

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Mysql table indexes
Posted: Wed May 06, 2020 05:30 PM

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

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

FranciscoA

Posts: 2170

Joined: Fri Jul 18, 2008 01:24 AM

Re: Mysql table indexes
Posted: Wed May 06, 2020 08:31 PM

Try this way:

Code (fw): Select all Collapse

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

Regards.

Francisco J. Alegría P.

Chinandega, Nicaragua.

Fwxh-MySql-TMySql

FranciscoA

Posts: 2170

Joined: Fri Jul 18, 2008 01:24 AM

Re: Mysql table indexes
Posted: Wed May 06, 2020 08:47 PM

Otra manera:

Code (fw): Select all Collapse

//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.

Francisco J. Alegría P.

Chinandega, Nicaragua.

Fwxh-MySql-TMySql

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Re: Mysql table indexes
Posted: Wed May 06, 2020 11:24 PM

Thank you

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Mysql table indexes
Posted: Thu May 07, 2020 02:47 AM

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

Code (fw): Select all Collapse

aIndexes := oCn:ListIndexes( cTable )

Regards

G. N. Rao.

Hyderabad, India

vilian

Posts: 1067

Joined: Wed Nov 09, 2005 02:17 AM

Re: Mysql table indexes
Posted: Thu May 07, 2020 11:13 AM

Thank you

Sds,
Vilian F. Arraes
vilian@vfatec.com.br
Belém-Pa-Brazil

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: Mysql table indexes
Posted: Thu May 07, 2020 12:07 PM

nageswaragunupudi wrote:
vilian wrote:Hi Guys,
How could I know the indexes of a mysql table has ?
Code (fw): Select all Collapse
aIndexes := oCn:ListIndexes( cTable )

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

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Mysql table indexes
Posted: Thu May 07, 2020 12:24 PM

Is there any function to create index?

No.

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

Code (fw): Select all Collapse

METHOD MakePrimaryKey( cTable, cCol )

Regards

G. N. Rao.

Hyderabad, India

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: Mysql table indexes
Posted: Thu May 07, 2020 12:32 PM

nageswaragunupudi wrote:
Is there any function to create index?

No.

Not exactly but this function automatically creates a primary key index:
Code (fw): Select all Collapse
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?

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Mysql table indexes
Posted: Thu May 07, 2020 01:16 PM

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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

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.

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion