CODEWITHSUNDEEP
sqlmysqlviewmaterialized-views
yegor256
yegor256

Reputation: 105043

MySQL VIEW vs. embedded query, which one is faster?

I'm going to optimize a MySQL embedded query with a view, but I'm not sure whether it will give an effect:

SELECT id FROM (SELECT * FROM t);

I want to convert it to:

CREATE VIEW v AS SELECT * FROM t; 
SELECT id FROM v;

I've heard about "indexed views" in SQL Server, but I'm not sure about MySQL. Any help would be appreciated. Thanks!

Upvotes: 3

Views: 2386

Answers (4)

OMG Ponies
OMG Ponies

Reputation: 332521

Indexed views in SQL Server are generally called "materialized views", which MySQL does not support. MySQL's VIEW support is rather limited in comparison to other vendors - the restrictions are listed in their documentation.

A normal view is merely a prepared SQL statement - there's no difference between using the two examples you provided. In some cases, the WHERE clause when selecting from a View can be pushed into the VIEW query by the optimizer, but it's completely out of your control.

Upvotes: 2

simendsjo
simendsjo

Reputation: 4749

The select statement will be run each time you fetch a view.

A view behaves a bit differently, see Create View

Upvotes: 0

mr.b
mr.b

Reputation: 4962

It's about the same. Will it be fast or not it depends on your indexes.

MySQL caches query results, so as long as your queries are same between executions, and as long as underlying dataset is same (no new records added), it will return cached results on next query execution.

Upvotes: 0

Martijn Engler
Martijn Engler

Reputation: 106

The view might be faster (it probably is), but why don't you just test it? Or run an EXPLAIN against both queries to see how they will execute?

Upvotes: 0

Related Questions