Page 1 of 1
SQL Index file
Posted: Fri 13 Jul 2012 05:52
by marsheng
I have several indexes for my SQl table. The table IndexDefs retreives the list from the SQL database.
I'm not sure how to set a table to a specific index?
eg Table1 has fields names and addressses. The indexes are nameI and addI
Table1.Active:=True;
Table1 set index to nameI ??
I have used table1.IndexFieldNames:='Name' but then my app is handeling the index and I would rather the SQl server does it all.
Re: SQL Index file
Posted: Fri 13 Jul 2012 08:30
by AndreyZ
Hello,
To make MySQL use the particular index, you should use the "USE INDEX" SQL statement. Here is an example:
Code: Select all
MyQuery.SQL.Text := 'SELECT * FROM tablename USE INDEX (indexname)';
MyQuery.Open;
For more information, please read the following article:
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
Re: SQL Index file
Posted: Sat 21 Jul 2012 06:26
by marsheng
Sorry I forgot to tick notify if reply.
I don't want to modify the SQL statement, I want to use Delphi's inbuilt properties for the sort order.
If I use the drop down box of IndexDefs on my table, the list of availiable indexes are shown, however selecting any of them does not change the table's data.
How do I select a table index and make it active using properties?
Re: SQL Index file
Posted: Mon 23 Jul 2012 09:46
by AndreyZ
The TMyTable.IndexDefs property contains information about the indexes of a table, and it can be used only for viewing the list of indexes already created for the table. To sort records, you can use the TMyTable.IndexFieldNames property that is used to get or set the list of fields on which the recordset is sorted. For more information, please refer to the MyDAC documentation.
Re: SQL Index file
Posted: Mon 23 Jul 2012 10:31
by marsheng
You are not answering my question or I'm not explaining it correclty.
I manage the SLQ indexes with an SQL GUI, eg MySQL workbench.
TMyTable.IndexDefs lists the indexes created by MySQL workbench
How do I set my table to use one of MYSql workbenches created Indexes?
TMyTable.IndexFieldNames creates a new index on field names. This is not what I want.
If I update the Index structure with MySQL workbench, I want every reference to that index in my program to be updated as well.
Re: SQL Index file
Posted: Mon 23 Jul 2012 12:53
by AndreyZ
For the time being MyDAC doesn't have such functionality. You can leave your suggestion at our UserVoice page (
http://devart.uservoice.com/forums/1046 ... components ), and if there are many votes for your suggestion, we will implement it.