Why does STRAIGHT_JOIN consume more CPU?

Question

Why does STRAIGHT_JOIN consume more CPU than a regular join? Do you have any idea?

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?

You might want to check the code after this comment / Topic Ids are OK, Getting Devices... /
Before this line there are some code about filling topic_ids to a temp table.

Here is the query:

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 > 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;

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.

MySQL 5.6.19a - innodb

What might be the reason?

Best regards.

Answer 1

A STRAIGHT_JOIN is used when you need to override MySQL's optimizer. You are telling it to ignore its own optimized execution path and instead rely on reading the tables in the order you have written them in the query.

99% of the time you don't want to use a straight_join. Just rely on MySQL to do its job and optimize the execution path for you. After all, any RDBMS worth its salt is going to be pretty decent at optimizing.

The few times you should use a straight_join are when you've already tested MySQL's optimization for a given query and found it lacking. In your case with this query, clearly your manual optimization using straight_join is not better than MySQL's baked in optimization.