title: Avoid UNION for two almost identical tables in MySQL
body: <p>I'm not very good at MySQL and i'm going to write a query to <strong>count messages sent by an user</strong>, based on its type and <code>is_auto</code> field.</p>
  
  <p>Messages can be of type "small text message" or "newsletter". I created two entities with a few fields that differs between them. The important one is <strong><code>messages_count</code> that is absent in table <code>newsletter</code> and it's used in the query</strong>:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `small_text_message` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `messages_count` int(11) NOT NULL,
    `username` varchar(255) NOT NULL,
    `method` varchar(255) NOT NULL,
    `content` longtext,
    `sent_at` datetime DEFAULT NULL,
    `status` varchar(255) NOT NULL,
    `recipients_count` int(11) NOT NULL,
    `customers_count` int(11) NOT NULL,
    `sheduled_at` datetime DEFAULT NULL,
    `sheduled_for` datetime DEFAULT NULL,
    `is_auto` tinyint(1) NOT NULL,
    `user_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p>And:</p>
  
  <pre><code>CREATE TABLE `newsletter` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `subject` varchar(78) DEFAULT NULL,
    `content` longtext,
    `sent_at` datetime DEFAULT NULL,
    `status` varchar(255) NOT NULL,
    `recipients_count` int(11) NOT NULL,
    `customers_count` int(11) NOT NULL,
    `sheduled_at` datetime DEFAULT NULL,
    `sheduled_for` datetime DEFAULT NULL,
    `is_auto` tinyint(1) NOT NULL,
    `user_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p>I ended up with a <code>UNION</code> query. <strong>Can this query be shortened or optimized since the only difference is <code>messages_count</code> that should be always 1 for <code>newsletter</code>?</strong></p>
  
  <pre><code>SELECT
  CONCAT('sms_', IF(is_auto = 0, 'user' , 'auto')) AS subtype,
  SUM(messages_count * (customers_count + recipients_count)) AS count
  FROM small_text_message WHERE status &lt;&gt; 'pending' AND user_id = 1
  GROUP BY is_auto
  UNION
  SELECT
  CONCAT('newsletter_', IF(is_auto = 0, 'user' , 'auto')) AS subtype,
  SUM(customers_count + recipients_count) AS count
  FROM newsletter WHERE status &lt;&gt; 'pending' AND user_id = 1
  GROUP BY is_auto
  </code></pre>