0

Suppose we have two tables like A {a1, a2, a3 ...} and B{b1, b2, b3 ...}

We are using a MySQl view like

Select a.a1, b.b1 from A a Left JOIN  B b ON a.a2=b.b2 

Now our requirement is that we have to put "where conditions" dynamically on some columns of A and B, so we have to again join table and putting a where condition (In current code).

Now as joining twice is increasing our query execution time and I am trying to reduce the time.

We can't remove the view as per the security requirement (SQL SECURITY).

Is there a way to handle this scenario, I thought of using functions with "SQL SECURITY" or there are any other alternatives.

5
  • What MySQL version? What are the indexes on A,B? What does your EXPLAIN {query} show? – danblack Feb 27 '19 at 6:04
  • Our View is working fine.... In hibernate when using view.. we have to write like "Select * from view v left join A a on v.a=a.a1 where a.a3=something".. so the resulting query is using join twice when seen from Explain – Saurabh Singh Feb 27 '19 at 6:07
  • Use stored procedure instead of view. – Akina Feb 27 '19 at 6:30
  • @Akina Thanks but We already thought of using Functions... I asked here to consider any better alternative or if I am missing anything. – Saurabh Singh Feb 27 '19 at 6:33
  • Function will not help. Views do not allow parameters and UDVs using - both directly and in chained UDF – Akina Feb 27 '19 at 8:19
0

Try a newer version of MySQL. The view implementation in MySQL-5.5 was very simplistic. I've seen later versions take the view expression into account when doing query planning.

3
  • That's not possible as it might have large impact and we can't take it now. – Saurabh Singh Feb 27 '19 at 6:15
  • Of course, the large impact could be positive :-) – danblack Feb 27 '19 at 6:46
  • 1
    @SaurabhSingh - If you are afraid of it, then don't use VIEWs. They are just syntactic sugar; they don't improve performance over the equivalent SELECT. – Rick James Mar 7 '19 at 4:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.