CODEWITHSUNDEEP
mysqldatabasequery-optimization
Heru S
Heru S

Reputation: 1323

Should I be using CREATE VIEW instead of JOIN all the time

I have the following query:

SELECT t.*, a.hits AS ahits 
                FROM t, a 
                WHERE (t.TRACK LIKE 'xxx')
                AND a.A_ID = t.A_ID
                ORDER BY t.hits DESC, a.hits DESC

which runs very frequently. Table t has around 15M+ rows and a has around 3M+ rows.

When I did an EXPLAIN on the above query, I received a note saying that it always created a temp table. I noticed that creating a temp table based on the above query took quite a while. And, this is done plenty of time.

Thus, I am wondering if I create a view using the above say:

CREATE VIEW v_t_a 
SELECT t.*, a.hits AS ahits
FROM t, a
WHERE a.A_ID = t.A_ID

And change my code to:

SELECT * FROM v_t_a WHERE TRACK LIKE 'xxx' ORDER BY hits DESC, ahits DESC

Will it improve the performance? Will it remove the create temp table time?

Thank you so much for your suggestions!

Upvotes: 4

Views: 3049

Answers (3)

Ramandeep Singh
Ramandeep Singh

Reputation: 5253

VIEW internally JOINS the TWO tables everytime you QUERY a VIEW...!!

To prevent this, create MATERIALIZED VIEW... 

It is a view that is more of a TABLE ...You can query it directly as other table..

But you have to write some TRIGGERS to update it automatically, if any underlying TABLE data changes...

See this : http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views

Upvotes: 1

user1082916
user1082916

Reputation:

It is very dangerous if you assume MySQL would optimize your VIEWs same way as more advanced database systems would. Same as with subqueries and derived tables MySQL 5.0 will fail and perform very inefficiently in many counts.

MySQL has two ways of handling the VIEWS – query merge, in which case VIEW is simply expanded as a macro or Temporary Table in which case VIEW is materialized to temporary tables (without indexes !) which is later used further in query execution. There does not seems to be any optimizations applied to the query used for temporary table creation from the outer query and plus if you use more then one Temporary Tables views which you join together you may have serious issues because such tables do not get any indexes.

So be very careful implementing MySQL VIEWs in your application, especially ones which require temporary table execution method. VIEWs can be used with very small performance overhead but only in case they are used with caution.

MySQL has long way to go getting queries with VIEWs properly optimized.

Upvotes: 2

paxdiablo
paxdiablo

Reputation: 881103

It's rare that doing exactly the same operations in a view will be more efficient than doing it as a query.

The views are more to manage complexity of queries rather than performance, they simply perform the same actions at the back end as the query would have.

One exception to this is materialised query tables which actually create a separate long-lived table for the query so that subsequent queries are more efficient. I have no idea whether MySQL has such a thing, I'm a DB2 man myself :-)

But, you could possibly implement such a scheme yourself if performance of the query is an issue.

It depends greatly on the rate of change of the table. If the data is changing so often that a materialised query would have to be regenerated every time anyway, it won't be worth it.

Upvotes: 1

Related Questions