30.3.3 Filtering Query Analyzer Data

You can filter the queries shown within the Query Analyzer table by using the form at the top of the table. The different fields of the form are used to specify the parameters for the filter process. Once you have specified a filter, all the queries and related statistics shown within the Query Analyzer table are displayed in relation to the filter settings. For example, by default, the filter settings show the queries for the last 30 minutes. All the statistics shown are relative to the last 30 minutes, including average, maximum and execution counts.

The filtering functionality is available in a simple format, supporting simple statement and timing based filtering, and an advanced option allowing you to filter by specific columns within the Query Analyzer table.

Basic Filter Options

The basic filter options are:

Statement Text and Value support text searching of the normalized query. For the search type you can specify either a basic text match (Contains), or a regular expression match (Regex). In addition to the basic text match, you can also search for a query that does not contain a particular string. For regular expression searches, you can specify whether the regular expression should match, or not match (negative regexp) the queries. Regular expressions are parsed using the standard MySQL REGEXP() function. For more information, see Regular Expressions.
Note
The search is performed against the canonical version of the query. You cannot search against specific text or values within the parameters of the query itself.
Statement Type: Limits the search to statements of a particular type (SELECT, LITERAL, etc.).
DB Name: Limits the queries to those executed within a specific database. The database match is performed using the LIKE match from the MySQL database, hence you can use the % and _ characters to multiple and single character matches. For more information, see Pattern Matching.
The Time Range menu selects whether the time selection for filtering should be based on the time interval (only queries recorded within the displayed time period are shown, using the Hours and Minutes pop-up), or whether the selection should be based on a time period (From/To), where you can select the time range to be displayed.
Using the Interval mode shows queries within the given time period from the point the graph was updated. For example, if you select 30 minutes, then the queries shown were captured within the last 30 minutes. If you updated the display at 14:00, then the queries displayed would have been captured between 13:30 and 14:00. Using interval mode limits the timespan for the filter selection to a maximum of 23 hours and 59 minutes.
Using the From/To time range lets you show queries between specific dates and times. Using this mode you can show only the queries received during a specific time span, and you can display the query history for a much longer time period, for as long as you have been recording query analysis information.
Limit specifies the number of queries to be displayed within each page.

Advanced Filter Options

To use the advanced filtering techniques, click show advanced. This provides additional filters:

Notices: Filters on the notices column, allowing you to filter the list to show only the queries that did not raise a notice, indicated a full table scan, or indicated that a bad index was used.
Two column filters are provided, which allow you to filter the queries based on specific values within any of the columns shown in the Query Analyzer report list.
To use the column filters, you must specify the Column that you want to filter on, the Operator to use when performing the comparison and the Value that you want to compare.
For example, to filter by showing all the queries that return more than 100 rows on average, set the Column to Average Rows, the Operator to >=, and the Value to 100.
The View selection determines whether the information should be returned on a group basis, where an aggregate of the same query executed on all monitored servers is shown, or on a Server basis, where queries are summarized by individual server. If the latter option has been selected, the table includes an additional column showing the server.

All the filter settings that you specify are used collectively, that is, all the specified filter options are used to match against the list of queries.

When you have set your filter parameters, you can update the Query Analysis display by clicking the filter button. To reset the fields to the default settings click the reset button.

Named Filters for Query Analyzer

To create a named filter, you can either create a filter and save it by clicking Save as... menu item, or create a new filter by clicking New, defining your filter criteria, and clicking Save as....

The new named filter is marked with an asterisk (*) at the end of the name.

For more information, see Section 13.3.5, “System and User-defined Filters”.

Query Analyzer contains the following system filters:

Administration Statements: filters on statements of type GRANT, REVOKE, RESET, SET, SHOW, FLUSH, CACHE, KILL, and SHUTDOWN.
All Statements: (default) no filtering defined. All statements are displayed.
DDL Statements: filters on statements of type CREATE, DROP, ALTER, TRUNCATE, and RENAME.
DML Statements: filters on statements of type SELECT, INSERT, UPDATE, DELETE, REPLACE, CALL, LOAD, DO, and HANDLER.
Prepared Statements: filters on statements of type PREPARE, EXECUTE, and DEALLOCATE.
Replication Statements: filters on statements of type START, STOP, RESET, and CHANGE
Statements with Errors: filters on the advanced filter options of Total Errors > 0.
Statements with Full Table Scans: filters on the advanced filter options of Table Scan notices and Total Table Scans > 0.
Statements with Max Exec Time Over 1 Second: filters on the advanced filter options of Max Exec Time > 1.
Statements with Temporary Tables: filters on the advanced filter options of Total Temporary Tables > 0.
Statements with Temporary Tables on Disk: filters on the advanced filter options of Total Temporary Disk Tables > 0
Statements with Warnings: filters on the advanced filter options of Total Warnings > 0
Table Maintenance Statements: filters on statements of type OPTIMIZE, ANALYZE, CHECK, REPAIR, and CHECKSUM.
Transactional and Locking Statements: filters on statements of type BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE, LOCK, and UNLOCK.