I'm working on a project that contains a mysql database.
I want to create a view that makes queries much easier. But I got a problem. Since the tables that are used in the view contains each about 100.000 rows and I'm joining four of them, the view has to use the merge algorithm. But according to the MySQL documentaion:
MERGE cannot be used if the view contains any of the following
- Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
- GROUP BY
- UNION or UNION ALL
- Subquery in the select list
- Refers only to literal values (in this case, there is no underlying table)
EXIST( SELECT id FROM table WHERE col > val) AS flag
(SELECT id FROM payments WHER contract_id = val ORDER BY id DESC LIMIT 1) AS last_payment_id
CREATE VIEW viewName
EXIST( SELECT a FROM table2 t WHERE t2.date > NOW()) AS flag,
(SELECT id FROM table3 ORDER BY id DESC LIMIT 1) AS latest_id
Create a stored function with the
READS SQL DATA directives that accepts whatever arguments are needed to perform the subquery. Do the subquery inside the function, and return the result.
Then reference the function inside the view.
Counter-intuitive though it may seem, this abstraction allows the view to be declared with
ALGORITHM=MERGE, and processed accordingly, so you don't have the performance issues assocated with