MYSQL Index problem PRIMARY KEY index

[dre-logics]

I have Visual Basic 8.0 and Use Mysql 5.0 Database
Table Test:
Field type
ID Integer (PRIMARY KEY)
Name Char(10)
Adress Char(15)
City Char(15)
ID Name Adress City
1 Andre Street 1 New York
2 John Street 2 Amsterdam
3. Joyce Street 3 Londen

I have create a index on Column City:
CREATE INDEX CityIndex ON test (city)

I have now Select all record with the Index CityIndex Then the displayed list is still sorted on the PRIMARY key:

Select * from test use index (CityIndex)
I get this list:
ID Name Adress City
1 Andre Street 1 New York
2 John Street 2 Amsterdam
3. Joyce Street 3 Londen
4. Cees Street 4 Madrid
But I expect this list
ID Name Adress City
2 John Street 2 Amsterdam
3. Joyce Street 3 Londen
4. Cees Street 4 Madrid
1 Andre Street 1 New York

What am I doing wrong or am I forgotten?

Thanks in advance!!

Andre

[thecoat]

You might try:

SELECT * FROM test ORDER BY City

[dre-logics]

You might try:

SELECT * FROM test ORDER BY City

I know this works.
But why does not by the actual created index

[thecoat]

I know this works.
But why does not by the actual created index

What you are doing when you include the use index command is giving SQL a hint as to what index to use while it is executing your query. In more complex queries using specific indexes to retrieve data can sometimes be a performance boost. Generally you don't need to specify this because the SQL optimizer is relatively good about figuring out the most efficient index to use for a query.

Telling SQL a specific index or multiple indices for a query does not gaurantee what order the data will be returned in (and therefore have no effect on sorting). You could in code sort your datatable based on the index after the data has been retrieved, however as a general rule this is something you want SQL to handle for efficiency purposes.

If you are really keen on sorting the data in your app instead of SQL, look into the SORT method of the DefaultView property of the table:

table.DefaultView.Sort = "City"

[dre-logics]

What you are doing when you include the use index command is giving SQL a hint as to what index to use while it is executing your query. In more complex queries using specific indexes to retrieve data can sometimes be a performance boost. Generally you don't need to specify this because the SQL optimizer is relatively good about figuring out the most efficient index to use for a query.

Telling SQL a specific index or multiple indices for a query does not gaurantee what order the data will be returned in (and therefore have no effect on sorting). You could in code sort your datatable based on the index after the data has been retrieved, however as a general rule this is something you want SQL to handle for efficiency purposes.

If you are really keen on sorting the data in your app instead of SQL, look into the SORT method of the DefaultView property of the table:

table.DefaultView.Sort = "City"

Thanks, no is verything clear to me!