In the context of MySQL’s EXPLAIN EXTENDED, ‘filtered’ is a vital metric that indicates the percentage of rows filtered by the table condition after the storage engine retrieves them. It provides insight into the efficiency of your query. A higher ‘filtered’ value suggests that a larger portion of rows passed the filter condition, implying better optimization.
Conversely, a lower ‘filtered’ percentage points to inefficiency, as many rows are retrieved but not used in the final result, flagging a potential area for improvement in query performance.
The ‘filtered’ field in the MySQL EXPLAIN EXTENDED output shows the estimated percentage of rows that will be filtered by the table condition. This number helps in understanding how effective the WHERE clause is at reducing the number of rows that need to be processed further. A higher percentage indicates fewer rows being filtered out, implying a less selective condition, whereas a lower percentage means more rows are excluded by the condition, signifying a more selective condition.
This can influence the performance of the query, with more selective conditions generally leading to better performance as fewer rows need to be processed in subsequent steps.
The ‘filtered’ metric in MySQL EXPLAIN EXTENDED indicates the estimated percentage of table rows that will be filtered by the query conditions during execution. Lower ‘filtered’ values suggest that a larger portion of rows needs examination, potentially reducing performance, while higher ‘filtered’ values are ideal as they indicate more efficient filtering. By examining ‘filtered’ values, one can identify queries that might benefit from optimized indexes or refined conditions, thus improving performance.
Essentially, it’s a diagnostic tool that highlights potential inefficiencies in the data retrieval process, guiding database administrators to tweak their queries for better results.
Consider a query to retrieve users from a specific country:
EXPLAIN EXTENDED SELECT * FROM users WHERE country = 'Latvia';
The ‘filtered’ value indicates the percentage of rows that pass the table conditions. If ‘filtered’ is 10%, it means 90% of rows are filtered out. In real-world terms, if the table has 100,000 rows, and 10% are filtered, only 10,000 rows match the condition.
For a more intricate query involving multiple tables:
EXPLAIN EXTENDED SELECT users.name, orders.amount FROM users JOIN orders ON users.id = orders.user_id WHERE orders.date >= '2023-01-01';
Here, ‘filtered’ values for both tables can help identify which part of the join is more selective. If users has a ‘filtered’ value of 50% and orders has 20%, it suggests the orders table’s condition is more selective, with 80% rows being filtered out based on the date.
Analyzing a query with a subquery:
EXPLAIN EXTENDED SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
The ‘filtered’ value for the subquery shows the percentage of rows meeting the AVG(price) condition. If filtered is 30%, 70% of rows do not meet the price condition.
In each example, ‘filtered’ helps diagnose query performance. Low ‘filtered’ values indicate effective filtering, reducing the result set early, which is generally beneficial for performance.
Identify Filtered Rows: Check the filtered value to see the percentage of rows that meet the WHERE clause conditions. A lower value indicates less selectivity and potential performance issues.
Optimize WHERE Clauses: Ensure conditions in the WHERE clause are sargable (Search Argument Able) to allow efficient index use.
Use Indexes: Create indexes on columns used in the WHERE clause to improve selectivity and reduce the number of rows scanned.
Analyze Join Orders: For queries involving joins, ensure the join order is optimal by placing the most selective conditions first.
Avoid Functions on Indexed Columns: Using functions on indexed columns in the WHERE clause can prevent index usage.
Refactor Subqueries: Optimize subqueries to reduce the number of rows processed and improve overall query performance.
Monitor Query Execution Plans: Regularly review EXPLAIN EXTENDED output to identify and address performance bottlenecks.
The ‘filtered’ metric in MySQL EXPLAIN EXTENDED indicates the estimated percentage of table rows that will be filtered by the query conditions during execution.
A higher ‘filtered’ value suggests better optimization, while a lower value points to inefficiency and potential performance issues.
It helps diagnose query performance by identifying areas for improvement, such as: