The overhead of index creation is negligible compared to the cost of query execution without the index.Īs points out, MariaDB 5.3 has added the same optimization. In 5.6:įor cases when materialization is required for a subquery in the FROM clause, the optimizer may speed up access to the result by adding an index to the materialized table.Īfter adding the index, the optimizer can treat the materialized derived table the same as a usual table with an index, and it benefits similarly from the generated index. There might, however, be some light at the end of the tunnel on this issue of temporary tables not containing indexes (causing full table scans). Here is a really old blog post on the performance of views in MySQL and it doesn't seem to have gotten better. I would venture to guess your VIEWS are requiring the TEMPTABLE algorithm, causing performance issues. Refers only to literal values (in this case, there is no underlying table) MERGE cannot be used if the view contains any of the following constructs:Īggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth) If the MERGE algorithm cannot be used, a temporary table must be used instead. MySQL will attempt to use MERGE because it is more efficient. However, the fetch time is proportional to rows returned: 0.5 sec for 1M and and 5.0 sec for 10M rows. The 'third' option is UNDEFINED, which tells MySQL to select the appropriate algorithm. In MySQL Workbench's Duration / Fetch Time columns, the duration stays consistently under 1ms, regardless of the number of rows selected. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it. MERGE is simply a query expansion with appropriate aliases. Views in MySQL are handled using one of two different algorithms: MERGE or TEMPTABLE.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |