1

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. However, it consumes so much CPU. Might this 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.

1
  • Probably without the STRAIGHT_JOIN the query is disk-bound so spends time waiting for the IO (that does not consume CPU) and with it it does not have to wait so much so spends the time actually computing something. 90% is a lot but your query is not an easy one. Use EXPLAIN on both versions of the query and add the results to the question. – jkavalik Oct 27 '15 at 13:44
1

According to the MySQL Documentation on JOIN Syntax

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

The comment posted suggested using EXPLAIN on both versions of the query. Based on this suggestion and the join optimizer's current behavioral settings, the output of the two EXPLAIN plans should be noticeably different. Being more specific, the order of the tables in EXPLAIN plan of the STRAIGHT_JOIN will have tables being accessed in a different order from the other EXPLAIN plan of the regular join.

OPTIONAL SUGGESTIONS

You could change the optimizer switches to change its decision making process.

You could run one of the following switch settings with your regular JOIN:

SET optimizer_switch = 'batched_key_access=off,block_nested_loop=off'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=on'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=off'; SELECT ... JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=on'; SELECT ... JOIN

Then, you could run one of the following switch settings with your STRAIGHT_JOIN:

SET optimizer_switch = 'batched_key_access=off,block_nested_loop=off'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=off,block_nested_loop=on'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=off'; SELECT STRAIGHT JOIN
SET optimizer_switch = 'batched_key_access=on,block_nested_loop=on'; SELECT STRAIGHT JOIN

You could measure CPU and query performance for each of these eight(8) JOINs.

GIVE IT A TRY !!!

BTW other have good answers on STRAIGHT_JOIN

3
  • Hello Rolando, sorry for the late response. I've been so busy at work these days. I will try and give you feedback about it. Thank you so much. – Barbaros Alp Nov 2 '15 at 13:21
  • @BarbarosAlp - It's been 5 weeks; what did you find out? – Rick James Dec 7 '15 at 5:58
  • I couldnt try it yet. – Barbaros Alp Dec 7 '15 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.