MySQL Internals Optimizer/SergeyP Drafts
[edit] Benchmarking: Search for use cases
(Looking at queries done for MySQL and PostgreSQL's)
- DBT1 - No subqueries
- DBT2 - No subqueries
- DBT3 - Subquery cases:
- query 2: select ... where oe=(select min(..) ...)
- query 4: select ... where exists (correlated semi-join-ok select)
- query 7: select ... from (view-mergeable select) group by x order by y
- query 8: select ... from (view-mergeable select) group by x order by y
- query 11: select ... having sum(..) > (uncorrelated group by select)
- query 13: select ... from (group-by select)
- query 15: select ... where oe = (select max(col) from group-by view)
- query 16: select ... where oe not in (uncorrelated semi-join-ok select)
- query 17: select ... where oe < (select avg( ...) ... where correlated clause)
- query 18: select ... where oe in (uncorrelated group-by select)
- DBT4 - No subqueries
[edit] Index access methods chart
| Index access functions | Optimizer functions |
|---|---|
| multi_range_read_init(), multi_range_read_next() |
multi_range_read_info(), multi_range_read_info_const() |
| read_range_first(), read_range_next() |
records_in_range(), index_only_read_time(), read_time() |
| index_next_same() | |
| index_first(), index_last(), index_read_map(), index_read(), index_next(), index_prev() |