how to make MySql View Filter each table when fetched

Question

I have a view witch is work like so:

CREATE VIEW v_myView as SELECT * FROM(
      (SELECT a,b,c,d FROM table1)
    UNION ALL
      (SELECT a,b,c,d FROM table2)
    UNION ALL
      (SELECT a,b,c,d FROM table3)
    .
    .
    .)

When I use the view, I filter it like so:

SELECT * FROM v_myView WHERE a=x

While this works, it takes an hour(a real hour, not figurally speaking). if i'll do a query like so:

SELECT * FROM(
      (SELECT a,b,c,d FROM table1 WHERE a=x)
    UNION ALL
      (SELECT a,b,c,d FROM table2 WHERE a=x)
    UNION ALL
      (SELECT a,b,c,d FROM table3 WHERE a=x)
    .
    .
    .)

it takes a minute. that made me wonder if there is a way to make the MySql to do it automatically, meaning that the WHERE command will work before each table is fetched

score 3 · Accepted Answer · edited May 23 '17 at 10:26

As you might already know, the second method is faster because unlike the first it does not bring get all results from all three tables and then filter.

The answer from Leonard Strashnoy (https://stackoverflow.com/a/5331792/3996196) would be a way to solve this, but creating a function which defines your filtering variable.

This would look something like the following:

Function (Replace Int with your type)

create function p1() returns INTEGER DETERMINISTIC NO SQL return @p1;

View

CREATE VIEW v_myView as SELECT * FROM(
      (SELECT a,b,c,d FROM table1 WHERE a=p1() )
    UNION ALL
      (SELECT a,b,c,d FROM table2 WHERE a=p1() )
    UNION ALL
      (SELECT a,b,c,d FROM table3 WHERE a=p1() )
    .
    .
    .)

Query (Replace 12 with your variable)

SELECT * FROM (select @p1:=12 p) parm, v_myView;

I've used query-variables in MySQL queries before, but this is really a nice, elegant approach. It's not often that we need to produce more sophisticated queries, but - when we do - we can end up investing multiples of the time we spent acquiring accurate results on better performance. This answer appears to be worth remembering, so I'm following it. — quinny, Mar 31 '23 at 09:58

how to make MySql View Filter each table when fetched

1 Answers1