title: MySQL: Why would a query run faster with literal conditions compared to variables
body: <p>Not sure whether the actually query matters but, I have a MySQL Stored Procedure where I commented out the other parts of the proc except the following query...</p>
  
  <pre><code>INSERT INTO temp_attribution (`attribute_type`, `domain`, `id`, `name`, `score`, `rank`, `partner_match`, `person_match`, `sponsor_match`, `date_match`)
  SELECT 'Campaign' AS attribute_type, domain, id, name, score, (@proc_counter := @proc_counter + 1) AS rank,
      partner_match, person_match, sponsor_match, date_match
  FROM (
      SELECT m_c.domain, m_c.campaign_id AS id, m_c.name, m_c.client_id, m_c.sent_date,
          proc_sponsors AS invoice_sponsor, bs.sponsor AS campaign_sponsor, 
          proc_email AS invoice_email, aes_decrypt(m_r.email, in_encrypt_key) as campaign_email,
          if (m_c.client_id = proc_client_id COLLATE latin1_general_ci, 'Yes', 'No') AS partner_match,
          if (aes_encrypt(proc_email, in_encrypt_key) = m_r.email, 'Exact Email', 'Email Domain') AS person_match,
          if (LOCATE(CONVERT(bs.sponsor USING utf8mb4), proc_sponsors) &gt; 0, 'Sponsor',
              if (CONVERT(bs.vendor USING utf8mb4) = proc_vendor, 'Vendor', 'No') )  AS sponsor_match,
          if (datediff(proc_invoice_date, m_c.sent_date) BETWEEN 0 AND 92, 'Within Three', 'Within Six') AS date_match,
          (
              if (m_c.client_id = proc_client_id COLLATE latin1_general_ci, 45, 10) + 30 +
              if (LOCATE(CONVERT(bs.sponsor USING utf8mb4), proc_sponsors) &gt; 0, 10,
                  if (CONVERT(bs.vendor USING utf8mb4) = proc_vendor, 5, 0) ) +
              if (datediff(proc_invoice_date, m_c.sent_date) BETWEEN 0 AND 92, 15, 5)
          ) AS score 
      FROM campaign_table m_c
      INNER JOIN recipient_table m_r ON m_c.domain = m_r.domain AND m_c.campaign_id = m_r.campaign_id
      LEFT JOIN booking_sponsor bs ON m_c.domain = bs.domain AND m_c.campaign_id = bs.campaign_id
      WHERE datediff(proc_invoice_date, m_c.sent_date) BETWEEN 0 AND 185
      AND ( aes_encrypt(proc_email, in_encrypt_key) = m_r.email OR m_r.email_domain = proc_email_domain )
  ) T ORDER BY score DESC, sent_date DESC LIMIT 5;
  </code></pre>
  
  <p>The fields starting with 'proc_' are actually variables declared at the beginning of the procedure and this only takes 0.385 seconds to initialise whereas the entire proc takes 15 seconds.</p>
  
  <p>On a separate query window, I copied the relevant query and substituted variables starting with 'proc_' to test speed and optimise, like so...</p>
  
  <pre><code>INSERT INTO temp_attribution (`attribute_type`, `domain`, `id`, `name`, `score`, `rank`, `partner_match`, `person_match`, `sponsor_match`, `date_match`)
  SELECT 'Campaign' AS attribute_type, domain, id, name, score, (@proc_counter := @proc_counter + 1) AS rank,
      partner_match, person_match, sponsor_match, date_match
  FROM (
      SELECT m_c.domain, m_c.campaign_id AS id, m_c.name, m_c.client_id, m_c.sent_date,
          'VENDOR SPONSOR VALUE' AS invoice_sponsor, bs.sponsor AS campaign_sponsor, 
          'johnsmith@domain.com' AS invoice_email, aes_encrypt('johnsmith@domain.com', 'secret_key') as campaign_email,
          if (m_c.client_id = m_c.client_id COLLATE latin1_general_ci, 'Yes', 'No') AS partner_match,
          if (aes_encrypt('johnsmith@domain.com', 'secret_key'), 'Exact Email', 'Email Domain') AS person_match,
          if (LOCATE(CONVERT(bs.sponsor USING utf8mb4), 'VENDOR SPONSOR VALUE') &gt; 0, 'Sponsor',
              if (CONVERT(bs.vendor USING utf8mb4) = 'VENDOR', 'Vendor', 'No') )  AS sponsor_match,
          if (datediff('2016-10-14', m_c.sent_date) BETWEEN 0 AND 92, 'Within Three', 'Within Six') AS date_match,
          (
              if (m_c.client_id = m_c.client_id COLLATE latin1_general_ci, 45, 10) + 30 +
              if (LOCATE(CONVERT(bs.sponsor USING utf8mb4), 'VENDOR SPONSOR VALUE') &gt; 0, 10,
                  if (CONVERT(bs.vendor USING utf8mb4) = 'VENDOR', 5, 0) ) +
              if (datediff('2016-10-14', m_c.sent_date) BETWEEN 0 AND 92, 15, 5)
          ) AS score
      FROM campaign_table m_c
      INNER JOIN recipient_table m_r ON m_c.domain = m_r.domain AND m_c.campaign_id = m_r.campaign_id
      LEFT JOIN booking_sponsor bs ON m_c.domain = bs.domain AND m_c.campaign_id = bs.campaign_id
      WHERE datediff('2016-10-14', m_c.sent_date) BETWEEN 0 AND 185
      AND ( aes_encrypt('johnsmith@domain.com', 'secret_key') = m_r.email OR m_r.email_domain = 'domain.com' )
  ) T ORDER BY score DESC, sent_date DESC LIMIT 5;
  </code></pre>
  
  <p>Now, magically without doing anything else, the query runs within two seconds. How is that possible?</p>