title: Efficient performance and quick retrieval of record on MySQL database using a combination of UNION and JOIN query?
body: <p>The situation: I need to retrieve and display data on a webpage using PHP from three tables in a MySQL(version 8.0+) database running on a local server(appserve).</p>
  
  <p>The problem: I have a working query, but it is taking too much time to retrieve the data. I have 44,000+ records and my machine is Windows 7 64bit, Intel Core i3 with 4gb Memory(I mention this because, maybe the it is only natural for a lower machine to act in this manner). </p>
  
  <p>The first two table both consist of author(author of the book), author_id(unique auto increment id for author) and title(the title of the book) they are the same except for its ID, the ID on the first one is made of INT while the second one is VARCHAR so even I wanted to combine the I cannot, while the third table consist of other miscellaneous detail about the book, but in this example I only used the author_id and pub_date(book's publication date) to demonstrate the situation. </p>
  
  <p>A book can encounter multiple revision so the relationship between title and pub_date is one to many, but in the webpage I only want to display the most recent revision or publication date, hence the use of MAX and GROUP BY.</p>
  
  <p>My query is: </p>
  
  <pre><code>SELECT a.author, 
         a.title,
         a.author_id,  
         b.pub_date 
    FROM 
       ( SELECT author,
                title,
                author_id 
          FROM authors_table 
          UNION 
         SELECT author,
                title,
                author_id 
          FROM old_authors_table
         ) a 
      LEFT 
      JOIN ( SELECT author_id,
                    max(pub_date) AS pub_date 
      FROM details_table 
    GROUP 
      BY author_id
       ) b 
     ON b.author_id = a.author_id;
  </code></pre>