The filtered column shows an estimated percentage of rows that will be filtered by the table WHERE clause. It’s typically only shown during the EXPLAIN EXTENDED command.
SQL EXPLAIN: The “ref” column
The “ref” column shows which columns or constants are compared to the index named in the “key” column to select rows from the table. This comes right from the manual, but I have noticed that when the value is const,
SQL EXPLAIN: Index-related columns
These relate to the usage of indexes: possible_keys Which indexes were considered? key Which indexes did the optimizer choose? key_len How many bytes of the index will be used?
SQL EXPLAIN: The “type” column
The “type” column indicates how MySQL will access rows From worse to better: ALL index range ref eq_ref const system NULL
A faster alternative to correlated sub-queries
I recently had to rewrite a query that was running very slowly. It was taking over 30 seconds. Upon analyzing it, I saw that it had 2 correlated sub-queries in it, and each was doing a count(*). This meant that
SQL sub query challenge
Goal: Write a query which will get the first and last name from the customer table, and the total amount of all that customer’s orders. SELECT first,last FROM customers C This will get all the customers.Next we need to write
ACID
A.C.I.D. Atomicity Consistency Isolation Durability Atomicity in a database system means that a sequence of events within a transaction must all occur or nothing occurs. Because of this, the transaction cannot be observed by an outside process as “in progress”.