Announcement

Announcement Module
Collapse
No announcement yet.

ORDER BY ..LIMIT using indexes still slow

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ORDER BY ..LIMIT using indexes still slow

    Hello,

    I have a typical 3 table inner join with an ORDER BY and LIMIT clause that runs great for smaller data sets, but when the order by column has >800,000 rows it is take 2 minutes to complete. The EXPLAIN is showing Using temporary; Using filesort in the first table and I cannot seem to get rid of it. Any help would be great appracited.

    SQL statement:

    select distinct cm.message_datetime, c.keyword, cm.message_status, cm.num_recipients, cm.message_title
    from users_community_role ucr, community c, company_message cm
    where ucr.users_id=461
    and ucr.community_id=c.community_id
    and c.community_id=cm.community_id
    order by cm.company_message_id
    desc limit 5;

    /////////////////
    Here is the explain:
    ////////////////


    mysql> explain select distinct cm.message_datetime, c.keyword, cm.message_status, cm.num_recipients, cm.message_title from users_community_role ucr, community c, company_message cm where ucr.users_id=461 and ucr.community_id=c.community_id and c.community_id=cm.community_id order by cm.company_message_id desc limit 5\G;
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: ucr
    type: ref
    possible_keys: FK_users_community_role_1,FK_users_community_role_ 2,users_in d
    key: users_ind
    key_len: 8
    ref: const
    rows: 98
    Extra: Using where; Using index; Using temporary; Using filesort
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: c
    type: eq_ref
    possible_keys: PRIMARY,community_id
    key: community_id
    key_len: 8
    ref: ucr.community_id
    rows: 1
    Extra:
    *************************** 3. row ***************************
    id: 1
    select_type: SIMPLE
    table: cm
    type: ref
    possible_keys: comm_cm_index,company_message_community_id_fkey,co mm_cmmsgdt
    key: comm_cm_index
    key_len: 9
    ref: c.community_id
    rows: 59
    Extra: Using where
    3 rows in set (0.00 sec)


    /////////////////
    Here are the indexes:
    ////////////////////

    mysql> show index from users_community_role\G;
    *************************** 1. row ***************************
    Table: users_community_role
    Non_unique: 0
    Key_name: PRIMARY
    Seq_in_index: 1
    Column_name: users_community_role_id
    Collation: A
    Cardinality: 4204
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 2. row ***************************
    Table: users_community_role
    Non_unique: 1
    Key_name: FK_users_community_role_1
    Seq_in_index: 1
    Column_name: users_id
    Collation: A
    Cardinality: 89
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 3. row ***************************
    Table: users_community_role
    Non_unique: 1
    Key_name: FK_users_community_role_2
    Seq_in_index: 1
    Column_name: community_id
    Collation: A
    Cardinality: 2102
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 4. row ***************************
    Table: users_community_role
    Non_unique: 1
    Key_name: users_ind
    Seq_in_index: 1
    Column_name: users_id
    Collation: A
    Cardinality: 89
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 5. row ***************************
    Table: users_community_role
    Non_unique: 1
    Key_name: users_ind
    Seq_in_index: 2
    Column_name: community_id
    Collation: A
    Cardinality: 4204
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:



    *************************** 1. row ***************************
    Table: community
    Non_unique: 0
    Key_name: PRIMARY
    Seq_in_index: 1
    Column_name: community_id
    Collation: A
    Cardinality: 986
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 2. row ***************************
    Table: community
    Non_unique: 0
    Key_name: community_id
    Seq_in_index: 1
    Column_name: community_id
    Collation: A
    Cardinality: 986
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 3. row ***************************
    Table: community
    Non_unique: 1
    Key_name: community_company_id_fkey
    Seq_in_index: 1
    Column_name: company_id
    Collation: A
    Cardinality: 58
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    *************************** 4. row ***************************
    Table: community
    Non_unique: 1
    Key_name: FK_community_2
    Seq_in_index: 1
    Column_name: default_users_id
    Collation: A
    Cardinality: 1
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:



    *************************** 1. row ***************************
    Table: company_message
    Non_unique: 0
    Key_name: PRIMARY
    Seq_in_index: 1
    Column_name: company_message_id
    Collation: A
    Cardinality: 854352
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 2. row ***************************
    Table: company_message
    Non_unique: 0
    Key_name: company_message_id
    Seq_in_index: 1
    Column_name: company_message_id
    Collation: A
    Cardinality: 854352
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 3. row ***************************
    Table: company_message
    Non_unique: 0
    Key_name: comm_cm_index
    Seq_in_index: 1
    Column_name: community_id
    Collation: A
    Cardinality: 14480
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    *************************** 4. row ***************************
    Table: company_message
    Non_unique: 0
    Key_name: comm_cm_index
    Seq_in_index: 2
    Column_name: company_message_id
    Collation: A
    Cardinality: 854352
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 5. row ***************************
    Table: company_message
    Non_unique: 1
    Key_name: company_message_mobile_offer_id_fkey
    Seq_in_index: 1
    Column_name: mobile_offer_id
    Collation: A
    Cardinality: 17
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    *************************** 6. row ***************************
    Table: company_message
    Non_unique: 1
    Key_name: company_message_community_id_fkey
    Seq_in_index: 1
    Column_name: community_id
    Collation: A
    Cardinality: 17
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    *************************** 7. row ***************************
    Table: company_message
    Non_unique: 1
    Key_name: company_message_user_request_id_fkey
    Seq_in_index: 1
    Column_name: user_request_id
    Collation: A
    Cardinality: 17
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    *************************** 8. row ***************************
    Table: company_message
    Non_unique: 1
    Key_name: comp_message_message_date_index
    Seq_in_index: 1
    Column_name: message_datetime
    Collation: A
    Cardinality: 34174
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:
    *************************** 9. row ***************************
    Table: company_message
    Non_unique: 1
    Key_name: comm_cmmsgdt
    Seq_in_index: 1
    Column_name: community_id
    Collation: A
    Cardinality: 407
    Sub_part: NULL
    Packed: NULL
    Null: YES
    Index_type: BTREE
    Comment:
    *************************** 10. row ***************************
    Table: company_message
    Non_unique: 1
    Key_name: comm_cmmsgdt
    Seq_in_index: 2
    Column_name: message_datetime
    Collation: A
    Cardinality: 170870
    Sub_part: NULL
    Packed: NULL
    Null:
    Index_type: BTREE
    Comment:


    Thank you in advance and please let me know if I left out any critcal information.

    Thanks,

    Brian

  • #2
    The first optimization I can see is to move the ucr search by users_id to sub-query as below:

    select distinct cm.message_datetime, c.keyword, cm.message_status, cm.num_recipients, cm.message_titlefrom company_message cm, community cwhere cm.community_id IN (SELECT community_id FROM users_community_role ucr WHERE ucr.users_id=461)and cm.community_id=c.community_idorder by cm.company_message_iddesc limit 5;


    I could not see why you need a DISTINCT. So please post the tables structure then I can understand more.

    BTW, it seems that there're some redundant indexes that could be removed. Await the tables structure...

    Comment


    • #3
      Thanks for your reply.

      We need the DISTINCT because the users_community_role table is denormalized. We can have many users_id - community_id matching entries. We need this for some other optimizations but it is hurting us here and maybe we need to re-think that.

      FYI...here are table structures.

      CREATE TABLE `users_community_role` (
      `users_community_role_id` bigint(20) unsigned NOT NULL auto_increment,
      `community_id` bigint(20) unsigned NOT NULL default '0',
      `users_id` bigint(20) unsigned NOT NULL default '0',
      `uuid` varchar(36) NOT NULL default '',
      `role_name` varchar(255) default NULL,
      `group_name` varchar(255) default NULL,
      `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `date_updated` timestamp NOT NULL default '0000-00-00 00:00:00',
      PRIMARY KEY (`users_community_role_id`),
      KEY `FK_users_community_role_1` (`users_id`),
      KEY `FK_users_community_role_2` (`community_id`),
      CONSTRAINT `FK_users_community_role_1` FOREIGN KEY (`users_id`) REFERENCES `users` (`users_id`),
      CONSTRAINT `FK_users_community_role_2` FOREIGN KEY (`community_id`) REFERENCES `community` (`community_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


      CREATE TABLE `community` (
      `community_id` bigint(20) unsigned NOT NULL auto_increment,
      `keyword` varchar(100) default NULL,
      PRIMARY KEY (`community_id`),
      UNIQUE KEY `community_id` (`community_id`),
      KEY `FK_community_2` USING BTREE (`default_users_id`),
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 10240 kB;


      CREATE TABLE `company_message` (
      `company_message_id` bigint(20) unsigned NOT NULL auto_increment,
      `uuid` varchar(36) default NULL,
      `status` tinyint(4) default '0',
      `community_id` bigint(20) unsigned default NULL,
      `message_title` varchar(250) default NULL,
      `message_text` text NOT NULL,
      `message_status` varchar(50) default NULL,
      `message_datetime` timestamp NOT NULL default '0000-00-00 00:00:00',
      `num_recipients` bigint(20) unsigned default NULL,
      PRIMARY KEY (`company_message_id`),
      UNIQUE KEY `company_message_id` (`company_message_id`),
      KEY `company_message_community_id_fkey` (`community_id`),
      CONSTRAINT `company_message_community_id_fkey` FOREIGN KEY (`community_id`) REFERENCES `community` (`community_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
      ENGINE=InnoDB DEFAULT CHARSET=latin1;

      Comment


      • #4
        Yes, please do as follow:
        * table `users_community_role`: replace key `FK_users_community_role_1`(users_id) by (users_id, community_id)
        * table `company_message`:
        - remove UNIQUE KEY `company_message_id` (`company_message_id`) as it's redundant
        - replace key `company_message_community_id_fkey` (`community_id`) by (community_id, company_message_id)
        * try the query below:

        SELECT cm.message_datetime, c.keyword, cm.message_status, cm.num_recipients, cm.message_titleFROM company_message cm, community cWHERE cm.community_id IN (SELECT community_id FROM users_community_role ucr WHERE ucr.users_id=461)AND cm.community_id=c.community_idORDER BY cm.company_message_idDESC LIMIT 5;


        Hope it works )

        Comment


        • #5
          hi, guys, I am glad to join this thread, since I've encountered with the same problem.

          to safari:
          I've also tested your suggested sql, instead of using a filesort, mysql now use index on the order by column, but....It's still SLOW.



          select * from FileMirrors where md5 in(select md5 from MD5Keyword where keyword='mp3') order by mirrors desc limit 1000, 10


          in the upper sql, subquery from MD5Keyword produces about 30,000 records, I guess this is the main reason that produces poor performance.

          Comment


          • #6
            Not sure about your case But there should be an index on (md5, mirrors)!?

            Comment


            • #7
              yes, there is a index created with(md5, mirrors).

              here is the explain result:

              1 PRIMARY FileMirrors index \N NewIndex1 5 \N 1010 Using where; Using index

              2 DEPENDENT SUBQUERY MD5Keyword index_subquery NewIndex1,NewIndex2 NewIndex1 152 func 2 Using where

              see, the filesort has been got ridden of.
              But it is still slow.

              Comment


              • #8
                can you let me know some more information?

                what table type are you using for the table?
                if its MyISAM, please consider to optimize the key_buffer_size, join_buffer_size.
                if its InnoDB, innodb_buffer_pool_size should be considered.

                Hope this help.

                Comment


                • #9
                  hi, safari, thanks for your reply

                  The table DDL:
                  CREATE TABLE `MD5Keyword_new` (
                  `md5` varchar(50) NOT NULL,
                  `keyword` varchar(50) DEFAULT NULL,
                  KEY `NewIndex1` (`md5`),
                  KEY `NewIndex2` (`keyword`)
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


                  CREATE TABLE `FileMirrors` (
                  `md5` varchar(50) NOT NULL,
                  `mirrors` int(11) DEFAULT '0',
                  PRIMARY KEY (`md5`),
                  KEY `NewIndex1` (`mirrors`),
                  KEY `NewIndex2` (`md5`,`mirrors`)
                  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

                  2 facts need to be mentioned:
                  1. there are 380,000 records in MD5Keyword_new
                  2. there are 30,000 records in FileMirrors

                  the problem is,
                  when I executing the following query, it takes 30+ seconds.

                  select t1.*, t2.mirrors from MD5Keyword_new t1, FileMirrors t2 where t1.keyword = 'andy' and t2.md5=t1.md5 order by t2.mirrors desc limit 0, 10



                  explain it returns:


                  1 SIMPLE t1 ref NewIndex1,NewIndex2 NewIndex2 153 const 3512 Using where; Using temporary; Using filesort1 SIMPLE t2 eq_ref PRIMARY,NewIndex2 PRIMARY 152 MazeCommunity_Final.t1.md5 1




                  the FileMirrors.mirrors index seems not being used, so I tested another query:

                  select * from FileMirrors where md5 in (select md5 from MD5Keyword_new where keyword='andy') order by mirrors limit 0, 10



                  explain it returns:

                  1 PRIMARY FileMirrors index \N NewIndex1 5 \N 1010 Using where; Using index2 DEPENDENT SUBQUERY MD5Keyword_new index_subquery NewIndex1,NewIndex2 NewIndex1 152 func 2 Using where




                  now the index on FileMirrors.mirrors is being used, but unfortunately this query cost even longer time to return.


                  Any suggestion will be great!
                  Thank you.

                  Comment


                  • #10
                    got it.

                    the slowness comes from table MD5Keyword_new, please create an index on (keyword, md5) and use the query:

                    select * from FileMirrors where md5 in (select md5 from MD5Keyword_new where keyword='andy') order by mirrors limit 0, 10

                    Comment


                    • #11
                      after I created a index(keyword, md5) on MD5Keyword_new, the subquery become faster, the overall query is still slow, (

                      Comment


                      • #12
                        as in previous reply,

                        Quote:

                        what table type are you using for the table?
                        if its MyISAM, please consider to optimize the key_buffer_size, join_buffer_size.
                        if its InnoDB, innodb_buffer_pool_size should be considered.

                        Comment


                        • #13
                          hi, safari, I am not familiar with the innodb settings, I post it below, can you help to figure it out whether it's suitable?
                          thank you.


                          innodb_additional_mem_pool_size 1048576innodb_autoextend_increment 8innodb_autoinc_lock_mode 1innodb_buffer_pool_size 8388608innodb_checksums ONinnodb_commit_concurrency 0innodb_concurrency_tickets 500innodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir innodb_doublewrite ONinnodb_fast_shutdown 1innodb_file_io_threads 4innodb_file_per_table OFFinnodb_flush_log_at_trx_commit 1innodb_flush_method innodb_force_recovery 0innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFFinnodb_log_buffer_size 1048576innodb_log_file_size 5242880innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 90innodb_max_purge_lag 0innodb_mirrored_log_groups 1innodb_open_files 300innodb_rollback_on_timeout OFFinnodb_support_xa ONinnodb_sync_spin_loops 20innodb_table_locks ONinnodb_thread_concurrency 8innodb_thread_sleep_delay 10000innodb_use_adaptive_hash_indexes ON

                          Comment


                          • #14
                            To have better tuning, you have to know about your data and aplication: data size, heavy read or write, ....

                            The first try is to increase innodb_buffer_pool_size. This can be up to 80% of your system physical RAM, but this still depends on your data.

                            Using this tool http://rackerhacker.com/mysqltuner/ can help for a start.

                            Monitor database to assist you in database tuning also can help. Try this http://hackmysql.com/mysqlreport.

                            PS. one more good resource http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune- in-mysql-server-after-installation/

                            Regards,
                            Hung

                            Comment

                            Working...
                            X