Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is my example of query:

SELECT
    nickname, 
    CASE class_id
      WHEN 1 THEN 'Druid'
      WHEN 2 THEN 'Necromancer'
      WHEN 3 THEN 'Mage'
      WHEN 4 THEN 'Priest'
      WHEN 5 THEN 'Warrior'
      WHEN 6 THEN 'Stalker'
      WHEN 7 THEN 'Paladin'
      WHEN 8 THEN 'Psionic'
    END class_name,
    ROUND(AVG(level),2) level,
    ROUND(AVG(tabard_id),2) tabard,
    CASE rank_id
      WHEN 1 THEN 'Leader'
      WHEN 2 THEN 'Officer'
      WHEN 3 THEN 'Veteran'
      WHEN 4 THEN 'HonoryMember'
      WHEN 5 THEN 'OrdinaryMember'
      WHEN 6 THEN 'Alt'
      WHEN 7 THEN 'Apprentice'
      WHEN 8 THEN 'Penalty'
    END rank_name,
    ROUND(AVG(loyality),2) loyality,
    ROUND((MAX(authority)-MIN(authority))/AVG(tabard_id)) authority_effective,
    MAX(authority)-MIN(authority) authority_delta,
    MIN(authority) authority_begin,
    MAX(authority) authority_end
FROM users
    LEFT JOIN level_history ON level_history.users_id = users.id
    LEFT JOIN tabard_history ON tabard_history.users_id = users.id
    LEFT JOIN rank_history ON rank_history.users_id = users.id
    LEFT JOIN loyality_history ON loyality_history.users_id = users.id
    LEFT JOIN authority_history ON authority_history.users_id = users.id
    LEFT JOIN guilds_has_users ON guilds_has_users.users_id = users.id
    LEFT JOIN report ON report.id = authority_history.report_id
      AND report.id = level_history.report_id
      AND report.id = loyality_history.report_id
      AND report.id = rank_history.report_id
      AND report.id = tabard_history.report_id
WHERE report.date BETWEEN '2011-10-24 00:00:00' AND '2011-10-30 23:59:59'
  AND guilds_has_users.active = 1
GROUP BY users.id;

Explain of that select:

id  select_type   table               type    possible_keys                                            key                          key_len   ref                                           rows    Extra
1   SIMPLE        guilds_has_users    ref     fk_guilds_has_users_users1,active_IDX                    active_IDX                   1         const                                         139     Using where; Using temporary; Using filesort
1   SIMPLE        users               eq_ref  PRIMARY                                                  PRIMARY                      4         z92985_orlandino.guilds_has_users.users_id    1    
1   SIMPLE        level_history       ref     fk_level_history_users1,fk_level_history_report1,u...    fk_level_history_users1      4         z92985_orlandino.guilds_has_users.users_id    1       Using where
1   SIMPLE        report              eq_ref  PRIMARY,date_IDX,id_date_IDX                             PRIMARY                      4         z92985_orlandino.level_history.report_id      1       Using where
1   SIMPLE        tabard_history      ref     fk_tabard_history_users1,fk_tabard_history_report1...    fk_tabard_history_users1     4         z92985_orlandino.level_history.users_id       1       Using where
1   SIMPLE        rank_history        ref     fk_rank_history_users1,fk_rank_history_report1,use...    fk_rank_history_users1       4         z92985_orlandino.users.id                     1       Using where
1   SIMPLE        loyality_history    ref     fk_loyality_history_users1,fk_loyality_history_rep...    fk_loyality_history_users1   4         z92985_orlandino.rank_history.users_id        1       Using where
1   SIMPLE        authority_history   ref     fk_authority_history_users1,fk_authority_history_r...    fk_authority_history_users1  4         z92985_orlandino.level_history.users_id       1       Using where

Profiling of that select tells me:

(139 total, Query took 4.4918 sec)
Copying to tmp table 4.488318

And some information about MySQL variables:

SHOW VARIABLES LIKE '%buffer%';

Variable_name              Value
bulk_insert_buffer_size    8388608
join_buffer_size           131072
key_buffer_size            12884901888
myisam_sort_buffer_size    8388608
net_buffer_length          16384
preload_buffer_size        32768
read_buffer_size           131072
read_rnd_buffer_size       25165824
sort_buffer_size           2097144
sql_buffer_result          OFF

Why is copying to tmp table so slow? How to improve the speed of my query?

PS: I can't configure MySQL because my hosting provider won't allow it.

share|improve this question

2 Answers 2

You may need to try setting certain variables within your session

These particular values may be too small for your DB Connection to fulfill the query efficiently. These can be set within as follows:

  • To see what values these settings have currently do the following:
    • SHOW VARIABLES LIKE 'max_heap_table_size';
    • SHOW VARIABLES LIKE 'tmp_table_size';
  • To set max_heap_table_size to 64M do the following:
    • SET max_heap_table_size = 1024 * 1024 * 64;
  • To set tmp_table_size to 32M do the following:
    • SET tmp_table_size = 1024 * 1024 * 32;

Please consult the MySQL Documentation on Temp Table Usage

If you cannot set these values within your own session, contact your hosting provider to dynamically set them in your my.cnf.

Give it a Try !!!

share|improve this answer
    
+1 for setting the variables within the session, it certainly may help this query, which looks rather complicated... –  Dave Rix Nov 15 '11 at 23:17

Can you possibly reduce the query to only those tables absolutely necessary to produce your output, or split the query into multiple separate queries to pull out different parts of the information?

You may well find that running three separate queries on your data will be quicker than running one huge one - especially when your database starts to grown to tens and hundreds of thousands of rows.

I've also noticed from my own work that LEFT JOIN queries are not necessarily the most efficient, so only use them when absolutely necessary...

Hope that helps :)

share|improve this answer
    
Sometimes smaller queries make more sense than a heaving mass of tables and LEFT JOINs (Example : stackoverflow.com/questions/5983156/…) +1 !!! –  RolandoMySQLDBA Nov 15 '11 at 23:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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