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

Question

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!

Answer 1

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?

Answer 2

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.

Answer 3

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

A view behaves a bit differently, see Create View

Answer 4

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.