Re: Optimizing it even more

[Pepe Pardo]

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.