> Hi Sean,
>
> Thanks for your reply. I did it, and you are right - selectivity is much better.
> But, what is not better is query execution time.

...

> Query Time
> ------------------------------------------------
> Prepare       : 31.00 ms
> Execute       : 4,789.00 ms
> Avg fetch time: 228.05 ms

> Operations
> ------------------------------------------------
> Read : 3,049
> Writes : 0
> Fetches: 3,794
> Marks : 0

Are these values based on a fresh start of the database engine?

What is the timing for the 2nd run?

Have you checked that there is no active transaction (see db header stats) holding up garbage collection (causing the engine to have to resolve a large number of row versions)?

Also, some additional stats would be handy:
- How much RAM is in system?
- What is the size of the DB file?
- Page Size for the database?
- What are index stats of the LINK_LA_TYP INDEX (LINK_LA_TYP_IDX5)?
- What is the selectivity (Ratio count of distinct values vs. count of table rows) of the "ga_id" and "la_id" fields in LINK_LA_TYP table?


The "Read" operations shows that the query result in 3049 physical reads from "disk" (or OS cache), that works out to be 636 reads/sec, which for non-SSD devices is a pretty good rate.

Further, the "Fetch" vs. "Read" ratio shows that 80% of the reads were from "disk", so the speed of the hardware is a significant part of the problem.

All of which means that the "problem" is that a common issue -- there is a 'cost' to reading data from disk vs. cache/OS cache.


Sean