Forcing Index

[markhan]

Hi all!

Lets see if someone could help me with this...

Database: Access 2000

Indexes for log table: Ix(A,B,C,D) PrimaryKey(D,B,A,C)
I cannot change the index, so I have to use those ones

When im running this query:

SELECT A,B,C,D FROM log WHERE A='a' AND B='b' AND C='c' AND (D<d1) AND E>= e ORDER BY d

it needs many secods to retrieve the data.

I tried to change the where clause using D<'d1' And B='b' And A='a' And C='c' and E>= e
But it even needs more time.

So im not really sure if the SQL is using the indexes defined in the Access Table

So i was looking how to force the SQL to use an expecific Index.
In this case is better IX index than the primary Key Index.

I tried to use something like this: the example is in Msdn library
SELECT * FROM orders (index (0))
WHERE orderid BETWEEN 11000000 and 88000000
But didnt work. It gave Syntax error

I'll appreciate any help.

Thanks in advance.

Kind regards

 

[Qik3Coder]

It may be using the index. When was the last time the table was re-indexed, so that the rows are stored correctly on disk?

It's like defrag for your table.

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[markhan]

Thanks for the answer. After some changes, finally ,the index seems to work and my queries have been speeded up.

I cant reindex thats why i was asking about forcing indexes.

thank you so much

Regads