This is the example that Norvald Ryeng uses inMySQL EXPLAIN ANALYZE (MySQL Blog Archive). The query is a simple two-table join with aGROUP BYclause, which is why there is an aggregate (4) and temp table scan (5): SELECTfirst_name,last_name,SUM(amount)AStotalFROMstaffINNERJOINpaymentON...
For example: Start with ID = 0 and given STATEMENT_ID. Use the CONNECT BY clause to walk the tree from parent to child, the join keys being STATEMENT_ID = PRIOR STATEMENT_ID and PARENT_ID = PRIOR ID. Use the pseudo-column LEVEL (associated with CONNECT BY) to indent the children. ...
In the example in Figure27 below the where clause predicate is on the TIME table, which joins to the SALES table on the partition key TIME_ID. Partition pruning will happen at execution time after the WHERE clause predicate has been applied to the TIME table and the appropriate TIME_IDs ...
may be less than the number of columns present in the GROUP BY clause of the SQL statement if predicates eliminated the need to compare some columns. This argument can be associated with a GRPBY operator or with a TEMP operator when it is part of a query with multiple distinct aggregations...
A retrieval of a single row that is the result of applying a group function to a group of selected rows. UNIQUE An operation that sorts a set of rows to eliminate duplicates. GROUP BY An operation that sorts a set of rows into groups for a query with a GROUP BY clause. JOIN ...
Don't say, for example, 'Let me explain you about Jackie'. You can use explain with a that-clause to say that someone tells someone else the reason for something. I explained that I was trying to write a book. Collins COBUILD English Usage © HarperCollins Publishers 1992, 2004, 2011...
filtered- The amount of rows unfiltered by the conditions in the WHERE clause. These rows will be joined to the table in the next row of the EXPLAIN plan. As mentioned previously, this is a guesstimate as well, so MySQL can be wrong with this estimation. ...
A derived table may result, for example, from a subquery in the FROM clause. <subqueryN>: The row refers to the result of a materialized subquery for the row with an id value of N. See Section 8.2.2.2, “Optimizing Subqueries with Materialization”. partitions (JSON name: partitions)...
<unionM,N>: The row refers to the union of the rows with id values of M and N. <derivedN>: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause. <subqueryN>: The row ...
Scans whole table sequentially from beginning to end; also evaluates query constraints for every row (Filter) if specified with WHERE clause. Also used to run INSERT, UPDATE, and DELETE statements. JOINS: Amazon Redshift uses different join operators based on the physical design of the tables ...