AbcAeffchen AbcAeffchen - 1 year ago 61
MySQL Question

Creating a view without using subquerys in MySQL

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)






  • Subquery in the select list

  • Refers only to literal values (in this case, there is no underlying table)

So how I would insert an lookup, if something already exists

EXIST( SELECT id FROM table WHERE col > val) AS flag

or the latest id belonging to an other id

(SELECT id FROM payments WHER contract_id = val ORDER BY id DESC LIMIT 1) AS last_payment_id

into a view, without using a subquery? At the moment I try to avoid this. In some cases I could add a valid period of the rows (DATE valid_from, DATE valid_to) such that al periods are distinct for each contract_id, so it is unique and so I need no sorting or limiting. But this is realy ugly.

Is there a recommended way of doing such subqueries without a subquery in general?

Or do I just have to trick me around such things?

Notice: subqueries in the FROM clause are also forbidden, for all views, so that's also not an option.


I am looking for a way to get a view with the same results like

SELECT a.someColumn,
EXIST( SELECT a FROM table2 t WHERE > NOW()) AS flag,
(SELECT id FROM table3 ORDER BY id DESC LIMIT 1) AS latest_id
FROM table1

that uses the merge algorithm.

Answer Source

Create a stored function with the DETERMINISTIC and 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 TEMPTABLE.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download