CODEWITHSUNDEEP
mysql
Vikrant Parbalkar
Vikrant Parbalkar

Reputation: 3

Which Query is Optimized

I have employees table with 3 column id, name, salary, I have index to the salary column

EXPLAIN SELECT * FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees);

id  select_type   table        type    possible_keys  key     key_len  ref       rows  Extra                         
------  ------------  -----------  ------  -------------  ------  -------  ------  ------  ------------------------------
 1  PRIMARY       <subquery2>  system  (NULL)         (NULL)  (NULL)   (NULL)       1                                
 1  PRIMARY       employees    ALL     test           (NULL)  (NULL)   (NULL)       5  Using where                   
 2  MATERIALIZED  (NULL)       (NULL)  (NULL)         (NULL)  (NULL)   (NULL)  (NULL)  Select tables optimized away 



EXPLAIN SELECT * FROM employees  INNER JOIN (SELECT  MAX(salary) AS mx FROM employees) AS tbl ON tbl.mx=employees.salary;

id  select_type  table       type    possible_keys  key     key_len  ref       rows  Extra                         
------  -----------  ----------  ------  -------------  ------  -------  ------  ------  ------------------------------
 1  PRIMARY      <derived2>  system  (NULL)         (NULL)  (NULL)   (NULL)       1                                
 1  PRIMARY      employees   ALL     test           (NULL)  (NULL)   (NULL)       5  Using where                   
 2  DERIVED      (NULL)      (NULL)  (NULL)         (NULL)  (NULL)   (NULL)  (NULL)  Select tables optimized away

Which to use is it MATERIALIZED optmized or DERIVED,As MySQL needs the subquery result, it materializes that result into a temporary table.

And Derived Table is also a temporary table so which one is optimized ?? And Why ??

Thank you in Advance

Upvotes: 0

Views: 152

Answers (1)

Vatev
Vatev

Reputation: 7590

Both will have the same performance. MySQL transforms the first query into the second.

Select tables optimized away means that MySQL can "take a shortcut" and not read an actual table (SELECT MIN(indexed_field)).

In this plan there is a full scan on employees, probably because it only has 5 rows. You should add some more rows (and different salaries) to see what will actually happen.

Upvotes: 1

Related Questions