Question

1) Describe the performance impact of using the LIKE operation with a wild card character at the beginning of the value.

2) Under what circumstances is a non-matching index scan performed?

3) A query is written to access a single table. Furthermore, that query will return only a single row because an equality predicate is coded on the primary key for the table. A unique index exists to support the primary key. What type of access is likely to be the most efficient for that query?

4) How can stored procedures be used to optimize performance in a client/server application?

5) Under what circumstances will a table scan outperform indexed access?

Answer 1

Note: Done accordingly. Please comment for any problem. Please Uprate. Thanks

1)

LIKE operations with wildcard characters at the the beginning can be detrimental to database/application performance because there is no way to index a wild card. "Because the high-order portion of the column is not known, traversing a b-tree index structure is impossible." Here is a relevant quote from MySQL Internals Manual: “The optimizer will use an index (range search) for
column1 LIKE ’x%’
but not for
column1 LIKE ’%x’
That is, there is no range search if the first character in the pattern is a wildcard.”

2)

Nonmatching index scans are used when a starting point cannot be determined because the first column in the index key is not specified (does not start from root of b-tree.)

3)

A forced access path is likely to be used here since the table holds specific information for a single purpose as defined by business rules.

4)

If the network between the client and server cannot handle all of the traffic issuing SQL statements, then a stored procedure (which would issue on command on the client side, and execute several commands on the server is) can help with network performance.

5)

Table scans will be faster than index access scans when the database tables are extremely small.