How to interpret my ShowPlan(Execution Plan) How to interpret my ShowPlan(Execution Plan) database database

How to interpret my ShowPlan(Execution Plan)


in the second Showplan that means: I should put Index for Plauskomponente, Name, ID? and should I make a Composite Index From these three fields? How can I find that I should make a Composite index?

Those names appear in the ShowPlan section which shows the information Jet analyses when devising the query plan. Indexes on those 3 fields, either separate indexes or a single composite index based on all three, would not help that particular query. And actually adding indexes will slow down other operations ... when you add or delete rows, or edit values in indexed fields, the db engine must write changes to the table and to the composite index.

Optimizing indexing can be tricky. Indexes can speed up SELECT, but slow down INSERT, DELETE and UPDATE operations. You need to find the right balance for your application. If you're relatively new at this in Access, try the Performance Analyzer wizard from the database tools section of the menu. Examine the suggestions it offers. Many times those suggestions will involve indexes. You can add indexes it suggests, then drop them later if they degrade or don't improve your overall performance.

Why the Plauskommponente doesn't appear in the first showplan?

Beats me. I would guess the query planner already found the Names index, so considered it pointless to look for other indexes.

However, that brings up another important point. The table includes 1,553 rows, but Plauskomponente contains only 3 different values. With such low variability, an index on Plauskomponente would probably not even be used in a plan for a query which had a WHERE clause based on Plauskomponente. @Namphibian touched on the reason in a comment. Reading the index to find out which rows match the criterion, then reading the matching rows could be considered more costly than just ignoring the index and reading all the rows from the table.

Finally notice the statistics mentioned in the ShowPlan information sections. Those statistics are updated when you compact the database. So compacting is useful to give the query planner the latest information to use as it makes its decisions about how to optimize the query plan.


The query is reading the entire table. You don't have a where clause. It's unlikely you will be able to optimize it. What you could do is only select the columns you need. This reduce the amount of data being passed back. Adding a index will not speed up the query as you don't have a where clause. If you added a where clause and indexed those fields used in the where clause you might be able to optimise the query.