title: how to optimize below SQL query?
body: <p>I want to find an entity name that could be in one of the six tables. For that I have written a function <code>getEntityName(entityid)</code>. The code is working fine as I am using <code>UNION</code> for the tables. But the problem is, these tables contain a huge amount data so the function is taking a long time to return the output. I am not sure if by using <code>JOIN</code> I can optimize this function. I am sharing the function definition; please let me know if there is any way to optimize this function:</p>
  
  <pre><code>delimiter $$
  drop function if exists getEntityName$$
  create function getEntityName(entityid int) returns varchar(128)
      begin
  return(
          select
              shortname as entityname 
          from partnerships
           where partnerships.pshipentid = entityid 
          union
          select
              concat(lastname, ', ', firstname) as entityname
          from individuals
          where individuals.indentid = entityid 
          union
          select
              shortname as entityname
          from firms
          where firms.firmentid = entityid 
          union
          select
              shortname as entityname
          from funds
          where funds.fundentid = entityid 
          union
          select 
              entityshortname as entityname
          from deletedentities
          where deletedentities.entityid = entityid
      );
      end$$
  </code></pre>