title: Why does STRAIGHT_JOIN consume more CPU?
body: <p>Why does STRAIGHT_JOIN consume more CPU than a regular join? Do you have any idea? </p>
  
  <p>When i use straight_join on one of my queries, it speeds up the query like from 12 seconds to 3 seconds. But it consumes so much CPU? Might it be about server configuration or something else?</p>
  
  <p><em>You might want to check the code after this comment /</em> Topic Ids are OK, Getting Devices... <em>/ <br>
  Before this line there are some code about filling topic_ids to a temp table.</em></p>
  
  <p>Here is the query:</p>
  
  <pre><code>CREATE PROCEDURE `DevicesByTopic`(IN platform TINYINT, IN application TINYINT, IN topicList TEXT, IN page_no MEDIUMINT UNSIGNED)
  BEGIN
  
      DECLARE m_index INT DEFAULT 0;
      DECLARE m_topic VARCHAR(255);
      DECLARE m_topic_id BIGINT UNSIGNED DEFAULT NULL;
      DECLARE m_session_id VARCHAR(40) CHARSET utf8 COLLATE utf8_turkish_ci;
  
      -- Session Id 
      SET m_session_id = replace(uuid(), '-', '');    
  
      -- Temp table
      CREATE TEMPORARY TABLE IF NOT EXISTS tmp_topics(
          topic_slug VARCHAR(100) COLLATE utf8_turkish_ci
          ,topic_id BIGINT UNSIGNED
          ,session_id VARCHAR(40) COLLATE utf8_turkish_ci
          ,INDEX idx_tmp_topic_session_id (session_id)
          ,INDEX idx_tmp_topic_id (topic_id)
      ) CHARSET=utf8 COLLATE=utf8_turkish_ci; 
  
      -- Filling topics in a loop
      loop_topics: LOOP
          SET m_index = m_index + 1;      
  
          SET m_topic_id = NULL;      
          SET m_topic= SPLIT_STR(topicList,',', m_index);                     
  
          IF m_topic = '' THEN
              LEAVE loop_topics;      
          END IF;         
  
          SELECT t.topic_id INTO m_topic_id FROM topic AS t WHERE t.application = application AND (t.slug_hashed = UNHEX(MD5(m_topic)) AND t.slug = m_topic) LIMIT 1;
  
          -- Fill temp table
          IF m_topic_id IS NOT NULL AND m_topic_id &gt; 0 THEN           
              INSERT INTO tmp_topics 
                  (topic_slug, topic_id, session_id)           
              VALUES
                  (m_topic, m_topic_id, m_session_id);            
          END IF;
  
      END LOOP loop_topics;       
  
      /* Topic Ids are OK, Getting Devices... */
  
    SELECT 
      dr.device_id, dr.platform, dr.application, dr.unique_device_id, dr.amazon_arn   
    FROM 
      device AS dr
    INNER JOIN (
              SELECT STRAIGHT_JOIN      
              DISTINCT
                  d.device_id
              FROM
                  device AS d
              INNER JOIN 
                  device_user AS du ON du.device_id = d.device_id             
              INNER JOIN 
                  topic_device_user AS tdu ON tdu.device_user_id = du.device_user_id
              INNER JOIN
                  tmp_topics AS tmp_t ON tmp_t.topic_id = tdu.topic_id
              WHERE
                  ((platform IS NULL OR d.platform = platform) AND d.application = application)
                  AND d.page_no = page_no     
          AND d.status = 1
                  AND du.status = 1               
                  AND tmp_t.session_id = m_session_id COLLATE utf8_turkish_ci 
    ) dFiltered ON dFiltered.device_id = dr.device_id
    WHERE
          ((platform IS NULL OR dr.platform = platform) AND dr.application = application)
          AND dr.page_no = page_no        
          AND dr.status = 1;
  
      -- Delete rows fFill temp table
      DELETE FROM tmp_topics WHERE session_id = m_session_id;
  
  END;
  </code></pre>
  
  <p>With the STRAIGHT_JOIN this query takes about 3 seconds but consumes so much CPU like 90%, but if i remove the keyword "STRAIGHT_JOIN", it takes 12 seconds but consume 12% CPU. </p>
  
  <p>MySQL 5.6.19a - innodb</p>
  
  <p>What might be the reason?</p>
  
  <p>Best regards.</p>