MySQL Forums
Forum List  »  Stored Procedures

Re: Need support for MySQL stored procedure
Posted by: Peter Brawley
Date: January 22, 2019 12:25PM

> 1 how to use views

MySQL has two algorithms for processing a View--MERGE and TEMPTABLE. They each do what they say: MERGE merges the SQL of the View and of its underlying query; TEMPTABLE saves results of Views referenced in the query to temporary tables, then executes that.

If you don't specify ALGORITHM, MySQL will try to use MERGE. It decides it cannot if the query aggregates; or uses DISTINCT, GROUP BY, HAVING, LIMIT, UNION or user variables; or if a SELECT expression is a subquery; or has no table reference. If you specify MERGE and MySQL thinks it cannot use it, it will respond with a warning to that effect.

Some queries can be rewritten such that MySQL can use MERGE in Views that reference them. The most obvious case would be moving a subquery from the SELECT list to the FROM clause.

Other queries can't use MERGE, and when the query tends to slow its View will tend to slower. There you need a saved query mechanism, eg a queries table and an interface (ie an app or app module) for saving and executing.

> 2 sprocs do not usually improve performance, they are not an alternative to Views.

> 3 Dynamic sql in sprocs?

Yes

Post Q#4 in the java/jdbc forum, #6 in the json forum.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Need support for MySQL stored procedure
579
January 22, 2019 12:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.