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.

  • What MySQL version? What are the indexes on A,B? What does your EXPLAIN {query} show?
    – danblack
    Feb 27, 2019 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 Feb 27, 2019 at 6:07
  • Use stored procedure instead of view.
    – Akina
    Feb 27, 2019 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. Feb 27, 2019 at 6:33
  • Function will not help. Views do not allow parameters and UDVs using - both directly and in chained UDF
    – Akina
    Feb 27, 2019 at 8:19

1 Answer 1


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.

  • That's not possible as it might have large impact and we can't take it now. Feb 27, 2019 at 6:15
  • Of course, the large impact could be positive :-)
    – danblack
    Feb 27, 2019 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, 2019 at 4:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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