Working with the MySQL Temp Table algorithm to view

Question

It would be nice if the Temp Table algorithm was renamed to the Unscalable algorithm. Perhaps then it will give more warnings to developers when they see it in the definition of the definition - similarly when he speaks using the temp table in the explanation results. Just a rough request for the most part, but in fact it can be disastrous for ignorance.

The problem is that if you define certain things in your view, it will switch from a slow merge algorithm to a hopelessly inefficient temp table algorithm. This does not matter much if the data is small. But this is one of those things that will kill your productivity as your data grows.

What is the best way to handle this? This was a problem since the views were implemented more than 5 years ago, and I do not know of any attempts to fix it. Is there such a problem in other popular database systems?

Scroll down the link below to where it is discussed when the merge algorithm cannot be used to see what makes MySQL degenerate using the awful Temp Table algorithm: http://mysql2.mirrors-r-us.net/doc/refman /5.1/en/create-view.html

Why so bad? The temp table algorithm works like this:

• Run the “AS IS” view in the definition of the view without merging the request, which contains the criteria for the offer.
• Dump the resulting data into a temp table.
• Filter the data in the temp table based on the query, where the criteria for the offer - there are no indexes.

So, you can imagine that hell is involved here, when you have a 50 millionth row table with a definition of the form, for example: a stupid example, but you get the point:

create view last_order_date as select max(order_date) last_date, username from orders group by username; 

Then the user can write:

 select * from last_order_date where username = 'joejoe22' 

After 2 hours, the result returns ...

So what is the best way to deal with this situation?

Answer 1

Write a stored procedure that returns a result set.

 DELIMITER $$ CREATE PROCEDURE DoViewMyData(Param1 INT) BEGIN SELECT field1 as x ,field2 as y FROM table WHERE field1 = Param1; END$$ DELIMITER ; 

Now call the procedure, it will return the result set of the select statement inside.

 CALL DoViewMyData(1); OUTPUT: +------+-------------------+ | x | y | +------+-------------------+ | 1 | balabablabla | +------+-------------------+ 

Just execute CALL , not so SELECT CALL that does not work.
You can also not use CALL inside another SELECT statement.
You can, of course, instruct the stored procedure to output the output to a temporary table, and then use it in another SELECT.

The stored procedure will use the correct indexes and your select statement is already prepared.
In addition, you can prepare material in previous statements by speeding up your critical request.

You're right though: MySQL views are sure suck