row ******************* id: 1 select_type: SIMPLE table: rental type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 1 row in set (0.00 sec) One row per table.Ģ2 Approx # rows examined mysql> EXPLAIN SELECT first_name,last_name FROM customer LIMIT 10\G *************** 1. Key_len – longer keys = longer look up/ compare ref – shows what is compared, field or “const” Look closely if an index is not considered One row per table.Ģ1 Approx # rows examined mysql> EXPLAIN SELECT return_date SELECT return_date FROM rental WHERE rental_id=13534 System – system table, one value One row per table.Ģ0 EXPLAIN Plan indexes possible_keys key JOIN uses a unique index or key prefix Indexed fields compared with = One row per table.ġ9 Fastest Data Access Joining/looking up unique index values JOIN uses a non-unique index or key prefix Indexed fields compared with = != No NULL value possibilities Best data access strategy for non-unique values One row per table.ġ8 eq_ref Joining/looking up unique index values JOIN uses a non-unique index or key prefix Indexed fields compared with = != Extra pass for possible NULL values One row per table.ġ7 ref Joining/looking up non-unique index values Unique subquery using a PRIMARY/UNIQUE KEY of one table More about subqueries later One row per table.ġ5 type index_merge Use more than one indexĮxtra field shows more information sort_union, intersection, union One row per table.ġ6 ref_or_null Joining/looking up non-unique index values
MYSQL JOIN WITH A SUBQUERY FULL
Index = full index scan Scanning the entire data set? full index scan > full table scan (covering index) range = partial index scan, >= IS NULL, BETWEEN, IN One row per table.ġ4 type index_subquery using a non-unique index of one table row ******************* id: 1 select_type: SIMPLE table: rental type: const “Data access method” Get this as good as possible One row per table.ġ3 type ALL = full table scan Everything else uses an index row ******************* id: 1 select_type: SIMPLE table: rental - One per table/alias - NULL One row per table.ĮXPLAIN SELECT return_date FROM rental WHERE rental_id=0\G One row per table.ġ2 type “Data access method” Get this as good as possible Used in subqueries More on subqueries later DEPENDENT UNION DEPENDENT SUBQUERY DERIVED UNCACHEABLE SUBQUERY One row per table.
row ******************* id: 1 select_type: SIMPLE SIMPLE – one table, or JOINs PRIMARY First SELECT in a UNION Outer query of a subquery UNION, UNION RESULT One row per table. row ******************* id: 1 Id = sequential identifier One per table, subquery, derived table No row returned for a view Because it is virtual Underlying tables are represented One row per table.Ĩ select_type SIMPLE – one table, or JOINs PRIMARY row ******************* id: 1 select_type: SIMPLE table: rental type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: 1 row in set (0.00 sec) One row per table.ħ Id Id = sequential identifier One per table, subquery, derived table InnoDB - approx stats InnoDB - one method of doing dives into the data MyISAM has better/more accurate metadataĦ EXPLAIN Output EXPLAIN returns 10 fields: Possible and actual indexes used Length of index used Approx # of records examinedĥ Metadata Optimizer uses metadata: cardinality, # rows, etc. How data is looked up If there are subqueries, unions, sortsĤ What EXPLAIN Shows If WHERE, DISTINCT are used
MYSQL JOIN WITH A SUBQUERY UPDATE
UPDATE tbl SET fld1=“foo” WHERE fld2=“bar” can be changed to: EXPLAIN SELECT fld1 FROM tbl WHERE fld2=”bar” ģ What EXPLAIN Shows How many tables How tables are joined Sheeri Cabral Senior DB Admin/Architect, Mozilla Northeast PHP 2012Ģ EXPLAIN SQL extension SELECT only Can modify other statements:
Presentation on theme: "Optimizing MySQL Joins and Subqueries"- Presentation transcript:
0 kommentar(er)
