4

My normal query:

SELECT
    DISTINCT vt.id as id,
    vtt.name as n,
    vt.etxid as etx
FROM vt
LEFT JOIN vtt ON
    (vtt.locale = "etx"
    AND vtt.etxid = vt.etxid)

Execution time: 5ms

My view:

CREATE OR REPLACE VIEW myview AS
SELECT
    DISTINCT vt.id as id,
    vtt.name as n,
    vt.etxid as etx
FROM vt
LEFT JOIN vtt ON
    (vtt.locale = "etx"
    AND vtt.etxid = vt.etxid)

My view query:

SELECT * from myview;

Execution time: 600ms

Erik
  • 41
  • 5
  • Are you certain this is reproducible? The execution plans, and therefore running times, should be about the same, assuming the same underlying data. Has the underlying data changed? – Tim Biegeleisen Jul 10 '20 at 10:42
  • Just to observe, VIEWs in MySQL serve no useful purpose – Strawberry Jul 10 '20 at 10:45
  • The data have not changed. – Erik Jul 10 '20 at 10:47
  • found a far better dupe: https://stackoverflow.com/questions/2760475/mysql-view-performance – underscore_d Jul 10 '20 at 11:06
  • @Strawberry Views do indeed serve a very useful purpose in MySQL. If you are using a framework like Django, you can avoid using raw sql queries where they would otherwise be necessary by instead having your model point to a view with a precomposed query instead of a table. That way, you can keep using the regular queryset ORM and keep Django and sql separate. It is probably the same for other frameworks as well. – kloddant Nov 19 '21 at 17:00

1 Answers1

1

As soon as you mention DISTINCT or aggregation functions in a view MySQL selects TEMPTABLE algorithm for this view, and it means it will create a temporary table for the view and then apply sorting, grouping, and aggregations to it. See more details here. Also, there are some recommendations here concerning view performance.

slkorolev
  • 5,883
  • 1
  • 29
  • 32
  • I had marked this as a dupe of another Q or two, but then I realised none of them had as good (i.e. cited, non-speculative) an answer as this, so now I've duped them on this :-) – underscore_d Jul 10 '20 at 11:05