MySQL Forums
Forum List  »  Data Warehouse

Re: Optimizing it even more
Posted by: Pepe Pardo
Date: June 01, 2011 08:42AM

EXPLAIN
SELECT  DISTINCT a.*
    FROM  d_agents a
    INNER JOIN  f_sessions s ON a.agent_id = s.agent_fk
      AND  s.begin_timestamp >= '2010-01-01 00:00:00'
      AND  s.begin_timestamp <= '2011-05-25 23:59:59'
      AND  s.bot_fk IN (1,2,3,4,5,6,7,8,9,
               10,11,12,13,14,15,16,17,18,19,20,21,
               22,23,24,25,26,27,28,29,30,31,32);

1, 'SIMPLE', 's', 'ALL', '', '', '', '', 39825232, 'Using where; Using temporary'
1, 'SIMPLE', 'a', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'dw20.s.agent_fk', 1, ''

SELECT  DISTINCT a.*
    FROM  d_agents a
    INNER JOIN  f_sessions s ON a.agent_id = s.agent_fk
      AND  s.begin_timestamp >= '2010-01-01 00:00:00'
      AND  s.begin_timestamp <= '2011-05-25 23:59:59'
      AND  s.bot_fk IN (1,2,3,4,5,6,7,8,9,
               10,11,12,13,14,15,16,17,18,19,20,21,
               22,23,24,25,26,27,28,29,30,31,32);

1262513 rows fetched in 303,9812s(1817,2570s)

EXPLAIN
SELECT  a.*
    FROM  d_agents a
    INNER JOIN ( SELECT DISTINCT s.agent_fk
        FROM f_sessions s WHERE s.begin_timestamp >= '2010-01-01 00:00:00'
      AND  s.begin_timestamp <= '2011-05-25 23:59:59'
      AND  s.bot_fk IN (1,2,3,4,5,6,7,8,9,
               10,11,12,13,14,15,16,17,18,19,20,21,
               22,23,24,25,26,27,28,29,30,31,32)) z
    ON a.agent_id = z.agent_fk ;

1, 'PRIMARY', '<derived2>', 'ALL', '', '', '', '', 1262514, ''
1, 'PRIMARY', 'a', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'z.agent_fk', 1, ''
2, 'DERIVED', 's', 'ALL', '', '', '', '', 39825232, 'Using where; Using temporary'


SELECT  a.*
    FROM  d_agents a
    INNER JOIN ( SELECT DISTINCT s.agent_fk
        FROM f_sessions s WHERE s.begin_timestamp >= '2010-01-01 00:00:00'
      AND  s.begin_timestamp <= '2011-05-25 23:59:59'
      AND  s.bot_fk IN (1,2,3,4,5,6,7,8,9,
               10,11,12,13,14,15,16,17,18,19,20,21,
               22,23,24,25,26,27,28,29,30,31,32)) z
    ON a.agent_id = z.agent_fk;

1262513 rows fetched in 50,1569s(277,9720s)

So, as we can see the execution time of the query is so much better. Regarding the creation of a summary table, I'm gonna take into account for future releases but I think that is enough for us,
maybe we can optimize a little more in the my.ini file.

tmpdir = "E:/MySQL-5.5.11/temp/"
basedir="E:/MySQL-5.5.11/server/"
datadir="E:/MySQL-5.5.11/data/Data/"
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=256M
table_cache=256
tmp_table_size=2G
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=410M
key_buffer_size=319M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
#skip-innodb
innodb_additional_mem_pool_size=27M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=14M
innodb_buffer_pool_size=14G
innodb_log_file_size=652M
innodb_thread_concurrency=18

Do you see any further modifications we can do?


Kind regards.

Options: ReplyQuote


Subject
Views
Written By
Posted
6203
May 27, 2011 06:14AM
3036
May 28, 2011 10:49PM
2566
May 30, 2011 08:57AM
2853
May 30, 2011 07:15PM
2898
May 31, 2011 10:09AM
2861
May 31, 2011 08:53PM
Re: Optimizing it even more
2923
June 01, 2011 08:42AM
3099
June 01, 2011 09:15AM
2955
June 02, 2011 02:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.