If a query reads significantly more data than it returns, its query plan might be faulty. Perhaps no index is being used or the chosen index doesn't constrain the data examination well. You can use the following logging features with the output from EXPLAIN to diagnose query performance and optimize SQL statements.
Options for bad plan query logging are as follows:
The following command logs a bad plan event if 20 or more rows are read:
If a table is small enough to be scanned in memory, there might be no performance improvement obtained by adding an index to it. Recommendation: Set the log bad read threshold to 4000 to avoid logging small table scans, especially in production environments.
The session_log_bad_read_ratio variable controls the ratio of rows examined to rows used and logs any query returning results that exceed the specified limit. The following example sets the ratio to four.
For example, selecting a single value from an unindexed table containing 25 rows requires the DBMS to scan all 25 rows. If log bad read threshold is set lower than 25 (as in the preceding example), a message like the following is added to the log file:
May 1 18:28:15 alpha011 real: INSTR SID:57105409 db=paulm ac=Y sql="select * from x where id = 10" [Ok: 1 rows selected] time 0.5ms; reads: 3; inserts: 1; deletes: 0; updates: 0; counts: 1; rows_read: 25; forwards: 4; rows_output: 3; semaphore_matches: 0
Creating an index on the id column eliminates the need to scan the entire table to satisfy the query. After the table is indexed, the single-value SELECT is no longer logged as having a bad query plan.
However, a simple index might not always solve the problem. For example, if you create and populate two tables as follows:
Then query them using a join as follows:
This query is logged as having a bad plan:
May 1 20:10:34 alpha011 real: INSTR SID:57365505 db=paulm ac=Y sql="select * from x join y on x.id = y.x and y.id = 2" [Ok: 1 rows selected] time 178.6ms; reads: 4;inserts: 1; deletes: 0; updates: 0; counts: 1; rows_read: 26; forwards: 5; e0
Twenty-six rows were read but only one was selected. The EXPLAIN command displays the following statistics:
Based on statistics, the query planner has decided that the join index x on table y is not very unique, so it scans to find the rows in y where id equals 2, then compares against values from x. An index on y.id enables the planner to use the constraint y.id = 2 to pre-filter rows from y, as follows:
When run against the newly-indexed table, the preceding SELECT is no longer logged as having a bad query plan. The EXPLAIN command now displays the following statistics: