How can I identify missing indexes in ElevateDB?

In DBISAM, I use code to wrap a Query to request a plan, and then look
to see if it is not optimal:

> if Pos('UN-OPTIMIZED', xQuery.Plan.Text) > 0 then
>  LogReport('Query not optimal ' + xQuery.Plan.Text);

This works really well because if I do a "WHERE Column < 500" and the
table has no index, it logs it, and I can find and fix this.

In ElevateDB the Plan doesn't mention such things. I modified a table
to remove an index to see what the plan report would change do without
it, but nothing much changes - it doesn't mention how inefficient it is
or that there is a "all items scan" needed etc.

Is there a way I can achieve what I am wanting? I've always found this
to be a good way to spot cases where I've modified the SQL and not got
an index, causing inefficiency.

Thanks,

--

Matthew Jones

Matthew


It actually gives more help than DBISAM but its different. Post a plan or two and I'll see if I can point out the relevant bits.


Roy Lambert

Okay, Plan posted below. I removed a load of fields that don't matter,
and hacked some names to protect the innocent fields.

The key is that I dropped the index on "MyOrderState" which should
really be indexed IMO, but which doesn't get one.


--

Matthew Jones

========================================================================
========
SQL Query (Executed by ElevateDB 2.16 Build 2)
Note: The SQL  shown here is generated by ElevateDB and may not be
exactly the
same as the SQL that was originally entered.  However, none of the
differences
alter the execution results in any way.
========================================================================
========
SELECT ALL
"MyCreateTime" AS "MyCreateTime",
"MyOrderGUID" AS "MyOrderGUID",
"MyAddressShipJSON" AS "MyAddressShipJSON",
"MyOrderRef" AS "MyOrderRef",
"MyOrderState" AS "MyOrderState",
FROM "MyOrder" INNER JOIN "MyAccount" ON ("MyCustomerRef" =
"ACustomerRef")
WHERE ("MyOrderState" >= 200) AND ("MyAdminComplete" IS NULL)
ORDER BY "MyOrderNeedsShipping" DESC, "MyCreateTime"
Source Tables
-------------
CustomerOrder: 73 rows
CustomerAccount: 10 rows
Result Set
----------
The result set was insensitive and read-only
The result set consisted of zero or more rows
Filtering
---------
The following filter condition was applied to the CustomerOrder table:
("MyAdminComplete" IS NULL)
Index scan (CustomerOrder.idxAdminComplete): 21 keys, 2KB estimated cost
AND
("MyOrderState" >= 200)
Row scan (CustomerOrder): 21 rows, 8.7KB estimated cost
Joins
-----
The driver table was the CustomerOrder table
The CustomerOrder table was joined to the CustomerAccount table with
the inner
join expression:
("MyCustomerRef" = "YourCustomerRef")
The optimizer attempted to re-order the joins to a more optimal order
The joins were already in the most optimal order
The following join condition was applied to the CustomerAccount table:
("caCustomerRef" = "MyCustomerRef")
Index scan (CustomerAccount.idxCustomerRef)
Result set I/O statistics
-------------------------
Total rows visited: 2
Row buffer manager
Max buffer size: 1023.75KB Buffer size: 2.84KB
Hits: 2   Misses: 0   Hit ratio: 1
Reads: 0   read: 0B
Writes: 0   written: 0B
Index Page buffer manager
Max buffer size: 2MB Buffer size: 8KB
Hits: 2   Misses: 0   Hit ratio: 1
Reads: 0   read: 0B
Writes: 0   written: 0B
BLOB Block buffer manager
Max buffer size: 2MB Buffer size: 8KB
Hits: 8   Misses: 0   Hit ratio: 1
Reads: 0   read: 0B
Writes: 0   written: 0B
========================================================================
========
2 row(s) returned in 0 secs
========================================================================
========

Matthew,

you should search for

"Row scan"

That indicates that all rows are scanned, in many cases because of a
missing index.

Uli

Matthew


As Uli says look for Row Scan, but equally important is look for the estimated cost. Also, I'm not sure if I'm right here but the sizes of the table are important

Source Tables
-------------
CustomerOrder: 73 rows
CustomerAccount: 10 rows

Nothing much you do with tables of this size is going to make much difference. Try with a few thousand rows and you may get a difference (at least that's how I'd do it if I were Tim)

What follows is my guessing, and how I interpret it. Reality may well disagree

>Filtering
>---------
>The following filter condition was applied to the CustomerOrder table:
>("MyAdminComplete" IS NULL)
>Index scan (CustomerOrder.idxAdminComplete): 21 keys, 2KB estimated cost
>AND
>("MyOrderState" >= 200)
>Row scan (CustomerOrder): 21 rows, 8.7KB estimated cost

What this tells us is that first of all the tables were filtered by MyAdminComplete IS NULL that selection was carried out using the index CustomerOrder.idxAdminComplete and that it resulted in 21 rows for further checking and only 2KB of (index) data had to be examined to make the selection.

Next the 21 rows that came from the first filter were examined on a row by row basis to see if they should be included. SO 21 rows were checked and that involved comparing 8.7KB of data.

Now we move on to the realm of metaphysics

If an index existed for MyOrderState I would guess that it wouldn't be used anyway. The costs of checking that index, creating a list of rows to include in the result set, checking the idxAdminComplete index and creating a list of rows for inclusion and then combining the two lists excluding anything that wasn't in both to produce a list giving the rows for the final result set would be higher than simply doing a row scan.

If you have lots more data in the tables and the "MyAdminComplete" IS NULL test produced several thousand candidates for inclusion in the result set things may well change.

I am eagerly awaiting the day Tim has the time to write an article on interpreting these beasties

Roy Lambert

Roy Lambert <roy@lybster.me.uk> wrote:
> As Uli says look for Row Scan, but equally important is look for the
> estimated cost. Also, I'm not sure if I'm right here but the sizes of the
> table are important
>  [snip]
> Nothing much you do with tables of this size is going to make much
> difference. Try with a few thousand rows and you may get a difference (at
> least that's how I'd do it if I were Tim)

Indeed, this is very much a test database and I expect it to get a lot
larger over time. The Row scan is what I will look for in my test, and will
work out indexes from that. Most times one index fixes loads of queries.

As for it working out costs, I'm happy for it to ditch the index if it
thinks that helps. The main thing is that I want it to have the option. I
will be getting others to test my shop more soon...

Thank you both.

--
Matthew Jones