mysql视图慢_MySql视图非常慢。为什么？

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

解决方案

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.