Query sometime not using index












0















Starting in the middle of last night (of course) I have a query that stops using an index and when that happens, it takes over an hour to complete vs. about 3 seconds when it uses the index. This query has been run for more than a year with no issues until last night.



What I have been able to figure out is that the query is using an index sometimes and not others; using explain. It has been slow for 2 hours, then fast for 1 hour and now slow again, etc.



When the query is running fast, explain tells me the query is using the key: builder_row_id



When it is running slow, it has no key and the Extra has: Using join buffer (Block Nested Loop)



Here are the 2 rows from explain, sorry about the formatting:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e8_,,ALL,builder_row_id,,,,1,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e8_,,ref,builder_row_id,builder_row_id,6,"my_db_name.e6_.builder_block_id,const",4,100,

SELECT
e0_.id AS id_0,
e0_.name AS name_1,
e0_.content_id AS content_id_2,
e0_.from_label AS from_label_3,
e0_.support_address AS support_address_4,
e0_.actual_from_label AS actual_from_label_5,
e0_.actual_from_address AS actual_from_address_6,
e0_.enable_wysiwyg AS enable_wysiwyg_7,
e0_.enable_conversation AS enable_conversation_8,
e0_.folder_id AS folder_id_9,
e0_.enable_transactional AS enable_transactional_10,
e0_.type_id AS type_id_11,
e0_.utm_content AS utm_content_12,
e1_.builder_style_id AS builder_style_id_13,
e1_.id AS id_14,
e1_.builder_style_key AS builder_style_key_15,
e1_.builder_style_value AS builder_style_value_16,
e1_.builder_style_delete_status AS builder_style_delete_status_17,
e2_.content_id AS content_id_18,
e2_.content_text AS content_text_19,
e2_.content_html AS content_html_20,
e2_.content_subject AS content_subject_21,
e2_.content_preview_png AS content_preview_png_22,
e3_.builder_region_id AS builder_region_id_23,
e3_.id AS id_24,
e3_.builder_region_name AS builder_region_name_25,
e3_.builder_region_type_id AS builder_region_type_id_26,
e3_.builder_region_delete_status AS builder_region_delete_status_27,
e3_.builder_region_sort_order AS builder_region_sort_order_28,
e3_.builder_ui_id AS builder_ui_id_29,
e4_.builder_region_style_id AS builder_region_style_id_30,
e4_.builder_region_id AS builder_region_id_31,
e4_.builder_region_style_key AS builder_region_style_key_32,
e4_.builder_region_style_value AS builder_region_style_value_33,
e4_.builder_ui_id AS builder_ui_id_34,
e4_.builder_region_style_delete_status AS builder_region_style_delete_status_35,
e5_.builder_row_id AS builder_row_id_36,
e5_.builder_region_id AS builder_region_id_37,
e5_.builder_row_type_id AS builder_row_type_id_38,
e5_.builder_row_delete_status AS builder_row_delete_status_39,
e5_.builder_row_sort_order AS builder_row_sort_order_40,
e5_.builder_ui_id AS builder_ui_id_41,
e6_.builder_block_id AS builder_block_id_42,
e6_.builder_row_id AS builder_row_id_43,
e6_.builder_block_type_id AS builder_block_type_id_44,
e6_.builder_block_delete_status AS builder_block_delete_status_45,
e6_.builder_block_sort_order AS builder_block_sort_order_46,
e6_.builder_ui_id AS builder_ui_id_47,
e7_.builder_block_attribute_id AS builder_block_attribute_id_48,
e7_.builder_block_id AS builder_block_id_49,
e7_.builder_block_attribute_key AS builder_block_attribute_key_50,
e7_.builder_block_attribute_value AS builder_block_attribute_value_51,
e7_.builder_block_attribute_delete_status AS builder_block_attribute_delete_status_52,
e8_.builder_column_id AS builder_column_id_53,
e8_.builder_block_id AS builder_block_id_54,
e8_.parent_builder_column_id AS parent_builder_column_id_55,
e8_.builder_column_type_id AS builder_column_type_id_56,
e8_.builder_column_delete_status AS builder_column_delete_status_57,
e8_.builder_column_sort_order AS builder_column_sort_order_58,
e8_.builder_ui_id AS builder_ui_id_59,
e9_.builder_column_style_id AS builder_column_style_id_60,
e9_.builder_column_style_key AS builder_column_style_key_61,
e9_.builder_column_style_value AS builder_column_style_value_62,
e9_.builder_ui_id AS builder_ui_id_63,
e9_.builder_column_style_delete_status AS builder_column_style_delete_status_64,
e10_.builder_column_attribute_id AS builder_column_attribute_id_65,
e10_.builder_column_attribute_key AS builder_column_attribute_key_66,
e10_.builder_column_attribute_value AS builder_column_attribute_value_67,
e10_.builder_ui_id AS builder_ui_id_68,
e10_.builder_column_attribute_delete_status AS builder_column_attribute_delete_status_69,
e11_.builder_column_conf_id AS builder_column_conf_id_70,
e11_.builder_column_conf_key AS builder_column_conf_key_71,
e11_.builder_column_conf_value AS builder_column_conf_value_72,
e11_.builder_ui_id AS builder_ui_id_73,
e11_.builder_column_conf_delete_status AS builder_column_conf_delete_status_74,
e12_.builder_column_id AS builder_column_id_75,
e12_.builder_block_id AS builder_block_id_76,
e12_.parent_builder_column_id AS parent_builder_column_id_77,
e12_.builder_column_type_id AS builder_column_type_id_78,
e12_.builder_column_delete_status AS builder_column_delete_status_79,
e12_.builder_column_sort_order AS builder_column_sort_order_80,
e12_.builder_ui_id AS builder_ui_id_81,
e0_.content_id AS content_id_82,
e0_.product_id AS product_id_83,
e0_.unsubscribe_message_id AS unsubscribe_message_id_84,
e0_.unsubscribe_language_id AS unsubscribe_language_id_85,
e0_.rbac_role_id AS rbac_role_id_86,
e0_.folder_id AS folder_id_87,
e1_.id AS id_88,
e3_.id AS id_89,
e4_.builder_region_id AS builder_region_id_90,
e5_.builder_region_id AS builder_region_id_91,
e6_.builder_row_id AS builder_row_id_92,
e7_.builder_block_id AS builder_block_id_93,
e8_.builder_block_id AS builder_block_id_94,
e8_.parent_builder_column_id AS parent_builder_column_id_95,
e9_.builder_column_id AS builder_column_id_96,
e10_.builder_column_id AS builder_column_id_97,
e11_.builder_column_id AS builder_column_id_98,
e12_.builder_block_id AS builder_block_id_99,
e12_.parent_builder_column_id AS parent_builder_column_id_100
FROM
email e0_
LEFT JOIN builder_style e1_ ON e0_.id = e1_.id AND (e1_.builder_style_delete_status = 0)
LEFT JOIN content e2_ ON e0_.content_id = e2_.content_id
LEFT JOIN builder_region e3_ ON e0_.id = e3_.id AND (e3_.builder_region_delete_status = 0)
LEFT JOIN builder_region_style e4_ ON e3_.builder_region_id = e4_.builder_region_id AND (e4_.builder_region_style_delete_status = 0)
LEFT JOIN builder_row e5_ ON e3_.builder_region_id = e5_.builder_region_id AND (e5_.builder_row_delete_status = 0)
LEFT JOIN builder_block e6_ ON e5_.builder_row_id = e6_.builder_row_id AND (e6_.builder_block_delete_status = 0)
LEFT JOIN builder_block_attribute e7_ ON e6_.builder_block_id = e7_.builder_block_id AND (e7_.builder_block_attribute_delete_status = 0)
LEFT JOIN builder_column e8_ ON e6_.builder_block_id = e8_.builder_block_id AND (e8_.builder_column_delete_status = 0)
LEFT JOIN builder_column_style e9_ ON e8_.builder_column_id = e9_.builder_column_id AND (e9_.builder_column_style_delete_status = 0)
LEFT JOIN builder_column_attribute e10_ ON e8_.builder_column_id = e10_.builder_column_id AND (e10_.builder_column_attribute_delete_status = 0)
LEFT JOIN builder_column_conf e11_ ON e8_.builder_column_id = e11_.builder_column_id AND (e11_.builder_column_conf_delete_status = 0) AND (e11_.builder_column_conf_delete_status = 0)
LEFT JOIN builder_column e12_ ON e8_.builder_column_id = e12_.parent_builder_column_id AND (e12_.builder_column_delete_status = 0)
WHERE
e0_.delete_status = 0
AND
e0_.product_id = xxxxx
AND
e0_.id = xxxxx
ORDER BY
e3_.builder_region_sort_order ASC,

e5_.builder_row_sort_order ASC,

e6_.builder_block_sort_order ASC,

e8_.builder_column_sort_order ASC


Can someone point me in the right direction on what is going on here?



Here is the full explain:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e0_,,const,PRIMARY,id,id_2,product_id,customer_id,delete_status,rbac_role_id,delete_status_2,PRIMARY,8,const,1,100,"Using temporary; Using filesort"
1,SIMPLE,e1_,,ref,id,id,5,const,const,3,100,Using where
1,SIMPLE,e2_,,const,PRIMARY,PRIMARY,8,const,1,100,
1,SIMPLE,e3_,,ref,delete,delete,5,const,const,4,100,Using where
1,SIMPLE,e4_,,ref,builder_region_id,builder_region_id,5,"my_db_name.e3_.builder_region_id,const',1,100,
1,SIMPLE,e5_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e3_.builder_region_id,const",1,100,
1,SIMPLE,e6_,,ref,builder_region_id,builder_region_id,6,"my_db_name.e5_.builder_row_id,const",1,100,
1,SIMPLE,e7_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e6_.builder_block_id,const",1,100,
1,SIMPLE,e8_,,ALL,builder_row_id,,,,8,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e9_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const',5,100,
1,SIMPLE,e10_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",2,100,
1,SIMPLE,e11_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",1,100,
1,SIMPLE,e12_,,ALL,child_col,,,,8,100,Using where; Using join buffer (Block Nested Loop)


I just realized I posted the wrong create table:



CREATE TABLE `builder_column` (
`builder_column_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`builder_block_id` int(11) unsigned DEFAULT NULL,
`builder_column_type_id` tinyint(3) NOT NULL,
`builder_column_delete_status` tinyint(1) unsigned NOT NULL,
`builder_column_sort_order` tinyint(3) unsigned NOT NULL,
`builder_column_create_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`builder_column_modify_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`builder_ui_id` varchar(200) DEFAULT '',
`parent_builder_column_id` int(11) unsigned DEFAULT NULL,
`builder_column_flags` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`builder_column_id`),
KEY `builder_row_id` (`builder_block_id`,`builder_column_delete_status`),
KEY `child_col` (`parent_builder_column_id`,`builder_column_delete_status`)
) ENGINE=TokuDB AUTO_INCREMENT=901184 DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_SNAPPY;


UPDATE:
I have found that if I add:



FORCE INDEX FOR JOIN (email_builder_row_id)


and



FORCE INDEX FOR JOIN (child_col)


it will use the index and returns fast.










share|improve this question
















bumped to the homepage by Community 5 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 1





    Please add the CREATE TABLE and the query command.

    – McNets
    Nov 3 '17 at 15:32











  • And do you also need the create tables from the other joined tables?

    – Jeff Ward
    Nov 3 '17 at 15:40











  • Ppl must know why sometimes index is not used.

    – McNets
    Nov 3 '17 at 15:41











  • Please edit this sort of information into the question; it allows you to include formatting, is much easier to read, and doesn't have a limit on how much you can include. Thanks!

    – RDFozz
    Nov 3 '17 at 15:43











  • I'm going to suggest that your data model is seriously flawed! You have table numbers going from 0 - 12 and column numbers from 1 - 100. Any time names have numbers after them like that, it's indicative of a big problem. It looks like just one horrendous mess! Please post the results of EXPLAIN EXTENDED <your_query_here> Is the table you wanted to give the DDL for builder_row or builder_column? Can you remove the incorrect one please?

    – Vérace
    Nov 3 '17 at 17:09


















0















Starting in the middle of last night (of course) I have a query that stops using an index and when that happens, it takes over an hour to complete vs. about 3 seconds when it uses the index. This query has been run for more than a year with no issues until last night.



What I have been able to figure out is that the query is using an index sometimes and not others; using explain. It has been slow for 2 hours, then fast for 1 hour and now slow again, etc.



When the query is running fast, explain tells me the query is using the key: builder_row_id



When it is running slow, it has no key and the Extra has: Using join buffer (Block Nested Loop)



Here are the 2 rows from explain, sorry about the formatting:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e8_,,ALL,builder_row_id,,,,1,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e8_,,ref,builder_row_id,builder_row_id,6,"my_db_name.e6_.builder_block_id,const",4,100,

SELECT
e0_.id AS id_0,
e0_.name AS name_1,
e0_.content_id AS content_id_2,
e0_.from_label AS from_label_3,
e0_.support_address AS support_address_4,
e0_.actual_from_label AS actual_from_label_5,
e0_.actual_from_address AS actual_from_address_6,
e0_.enable_wysiwyg AS enable_wysiwyg_7,
e0_.enable_conversation AS enable_conversation_8,
e0_.folder_id AS folder_id_9,
e0_.enable_transactional AS enable_transactional_10,
e0_.type_id AS type_id_11,
e0_.utm_content AS utm_content_12,
e1_.builder_style_id AS builder_style_id_13,
e1_.id AS id_14,
e1_.builder_style_key AS builder_style_key_15,
e1_.builder_style_value AS builder_style_value_16,
e1_.builder_style_delete_status AS builder_style_delete_status_17,
e2_.content_id AS content_id_18,
e2_.content_text AS content_text_19,
e2_.content_html AS content_html_20,
e2_.content_subject AS content_subject_21,
e2_.content_preview_png AS content_preview_png_22,
e3_.builder_region_id AS builder_region_id_23,
e3_.id AS id_24,
e3_.builder_region_name AS builder_region_name_25,
e3_.builder_region_type_id AS builder_region_type_id_26,
e3_.builder_region_delete_status AS builder_region_delete_status_27,
e3_.builder_region_sort_order AS builder_region_sort_order_28,
e3_.builder_ui_id AS builder_ui_id_29,
e4_.builder_region_style_id AS builder_region_style_id_30,
e4_.builder_region_id AS builder_region_id_31,
e4_.builder_region_style_key AS builder_region_style_key_32,
e4_.builder_region_style_value AS builder_region_style_value_33,
e4_.builder_ui_id AS builder_ui_id_34,
e4_.builder_region_style_delete_status AS builder_region_style_delete_status_35,
e5_.builder_row_id AS builder_row_id_36,
e5_.builder_region_id AS builder_region_id_37,
e5_.builder_row_type_id AS builder_row_type_id_38,
e5_.builder_row_delete_status AS builder_row_delete_status_39,
e5_.builder_row_sort_order AS builder_row_sort_order_40,
e5_.builder_ui_id AS builder_ui_id_41,
e6_.builder_block_id AS builder_block_id_42,
e6_.builder_row_id AS builder_row_id_43,
e6_.builder_block_type_id AS builder_block_type_id_44,
e6_.builder_block_delete_status AS builder_block_delete_status_45,
e6_.builder_block_sort_order AS builder_block_sort_order_46,
e6_.builder_ui_id AS builder_ui_id_47,
e7_.builder_block_attribute_id AS builder_block_attribute_id_48,
e7_.builder_block_id AS builder_block_id_49,
e7_.builder_block_attribute_key AS builder_block_attribute_key_50,
e7_.builder_block_attribute_value AS builder_block_attribute_value_51,
e7_.builder_block_attribute_delete_status AS builder_block_attribute_delete_status_52,
e8_.builder_column_id AS builder_column_id_53,
e8_.builder_block_id AS builder_block_id_54,
e8_.parent_builder_column_id AS parent_builder_column_id_55,
e8_.builder_column_type_id AS builder_column_type_id_56,
e8_.builder_column_delete_status AS builder_column_delete_status_57,
e8_.builder_column_sort_order AS builder_column_sort_order_58,
e8_.builder_ui_id AS builder_ui_id_59,
e9_.builder_column_style_id AS builder_column_style_id_60,
e9_.builder_column_style_key AS builder_column_style_key_61,
e9_.builder_column_style_value AS builder_column_style_value_62,
e9_.builder_ui_id AS builder_ui_id_63,
e9_.builder_column_style_delete_status AS builder_column_style_delete_status_64,
e10_.builder_column_attribute_id AS builder_column_attribute_id_65,
e10_.builder_column_attribute_key AS builder_column_attribute_key_66,
e10_.builder_column_attribute_value AS builder_column_attribute_value_67,
e10_.builder_ui_id AS builder_ui_id_68,
e10_.builder_column_attribute_delete_status AS builder_column_attribute_delete_status_69,
e11_.builder_column_conf_id AS builder_column_conf_id_70,
e11_.builder_column_conf_key AS builder_column_conf_key_71,
e11_.builder_column_conf_value AS builder_column_conf_value_72,
e11_.builder_ui_id AS builder_ui_id_73,
e11_.builder_column_conf_delete_status AS builder_column_conf_delete_status_74,
e12_.builder_column_id AS builder_column_id_75,
e12_.builder_block_id AS builder_block_id_76,
e12_.parent_builder_column_id AS parent_builder_column_id_77,
e12_.builder_column_type_id AS builder_column_type_id_78,
e12_.builder_column_delete_status AS builder_column_delete_status_79,
e12_.builder_column_sort_order AS builder_column_sort_order_80,
e12_.builder_ui_id AS builder_ui_id_81,
e0_.content_id AS content_id_82,
e0_.product_id AS product_id_83,
e0_.unsubscribe_message_id AS unsubscribe_message_id_84,
e0_.unsubscribe_language_id AS unsubscribe_language_id_85,
e0_.rbac_role_id AS rbac_role_id_86,
e0_.folder_id AS folder_id_87,
e1_.id AS id_88,
e3_.id AS id_89,
e4_.builder_region_id AS builder_region_id_90,
e5_.builder_region_id AS builder_region_id_91,
e6_.builder_row_id AS builder_row_id_92,
e7_.builder_block_id AS builder_block_id_93,
e8_.builder_block_id AS builder_block_id_94,
e8_.parent_builder_column_id AS parent_builder_column_id_95,
e9_.builder_column_id AS builder_column_id_96,
e10_.builder_column_id AS builder_column_id_97,
e11_.builder_column_id AS builder_column_id_98,
e12_.builder_block_id AS builder_block_id_99,
e12_.parent_builder_column_id AS parent_builder_column_id_100
FROM
email e0_
LEFT JOIN builder_style e1_ ON e0_.id = e1_.id AND (e1_.builder_style_delete_status = 0)
LEFT JOIN content e2_ ON e0_.content_id = e2_.content_id
LEFT JOIN builder_region e3_ ON e0_.id = e3_.id AND (e3_.builder_region_delete_status = 0)
LEFT JOIN builder_region_style e4_ ON e3_.builder_region_id = e4_.builder_region_id AND (e4_.builder_region_style_delete_status = 0)
LEFT JOIN builder_row e5_ ON e3_.builder_region_id = e5_.builder_region_id AND (e5_.builder_row_delete_status = 0)
LEFT JOIN builder_block e6_ ON e5_.builder_row_id = e6_.builder_row_id AND (e6_.builder_block_delete_status = 0)
LEFT JOIN builder_block_attribute e7_ ON e6_.builder_block_id = e7_.builder_block_id AND (e7_.builder_block_attribute_delete_status = 0)
LEFT JOIN builder_column e8_ ON e6_.builder_block_id = e8_.builder_block_id AND (e8_.builder_column_delete_status = 0)
LEFT JOIN builder_column_style e9_ ON e8_.builder_column_id = e9_.builder_column_id AND (e9_.builder_column_style_delete_status = 0)
LEFT JOIN builder_column_attribute e10_ ON e8_.builder_column_id = e10_.builder_column_id AND (e10_.builder_column_attribute_delete_status = 0)
LEFT JOIN builder_column_conf e11_ ON e8_.builder_column_id = e11_.builder_column_id AND (e11_.builder_column_conf_delete_status = 0) AND (e11_.builder_column_conf_delete_status = 0)
LEFT JOIN builder_column e12_ ON e8_.builder_column_id = e12_.parent_builder_column_id AND (e12_.builder_column_delete_status = 0)
WHERE
e0_.delete_status = 0
AND
e0_.product_id = xxxxx
AND
e0_.id = xxxxx
ORDER BY
e3_.builder_region_sort_order ASC,

e5_.builder_row_sort_order ASC,

e6_.builder_block_sort_order ASC,

e8_.builder_column_sort_order ASC


Can someone point me in the right direction on what is going on here?



Here is the full explain:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e0_,,const,PRIMARY,id,id_2,product_id,customer_id,delete_status,rbac_role_id,delete_status_2,PRIMARY,8,const,1,100,"Using temporary; Using filesort"
1,SIMPLE,e1_,,ref,id,id,5,const,const,3,100,Using where
1,SIMPLE,e2_,,const,PRIMARY,PRIMARY,8,const,1,100,
1,SIMPLE,e3_,,ref,delete,delete,5,const,const,4,100,Using where
1,SIMPLE,e4_,,ref,builder_region_id,builder_region_id,5,"my_db_name.e3_.builder_region_id,const',1,100,
1,SIMPLE,e5_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e3_.builder_region_id,const",1,100,
1,SIMPLE,e6_,,ref,builder_region_id,builder_region_id,6,"my_db_name.e5_.builder_row_id,const",1,100,
1,SIMPLE,e7_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e6_.builder_block_id,const",1,100,
1,SIMPLE,e8_,,ALL,builder_row_id,,,,8,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e9_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const',5,100,
1,SIMPLE,e10_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",2,100,
1,SIMPLE,e11_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",1,100,
1,SIMPLE,e12_,,ALL,child_col,,,,8,100,Using where; Using join buffer (Block Nested Loop)


I just realized I posted the wrong create table:



CREATE TABLE `builder_column` (
`builder_column_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`builder_block_id` int(11) unsigned DEFAULT NULL,
`builder_column_type_id` tinyint(3) NOT NULL,
`builder_column_delete_status` tinyint(1) unsigned NOT NULL,
`builder_column_sort_order` tinyint(3) unsigned NOT NULL,
`builder_column_create_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`builder_column_modify_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`builder_ui_id` varchar(200) DEFAULT '',
`parent_builder_column_id` int(11) unsigned DEFAULT NULL,
`builder_column_flags` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`builder_column_id`),
KEY `builder_row_id` (`builder_block_id`,`builder_column_delete_status`),
KEY `child_col` (`parent_builder_column_id`,`builder_column_delete_status`)
) ENGINE=TokuDB AUTO_INCREMENT=901184 DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_SNAPPY;


UPDATE:
I have found that if I add:



FORCE INDEX FOR JOIN (email_builder_row_id)


and



FORCE INDEX FOR JOIN (child_col)


it will use the index and returns fast.










share|improve this question
















bumped to the homepage by Community 5 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.











  • 1





    Please add the CREATE TABLE and the query command.

    – McNets
    Nov 3 '17 at 15:32











  • And do you also need the create tables from the other joined tables?

    – Jeff Ward
    Nov 3 '17 at 15:40











  • Ppl must know why sometimes index is not used.

    – McNets
    Nov 3 '17 at 15:41











  • Please edit this sort of information into the question; it allows you to include formatting, is much easier to read, and doesn't have a limit on how much you can include. Thanks!

    – RDFozz
    Nov 3 '17 at 15:43











  • I'm going to suggest that your data model is seriously flawed! You have table numbers going from 0 - 12 and column numbers from 1 - 100. Any time names have numbers after them like that, it's indicative of a big problem. It looks like just one horrendous mess! Please post the results of EXPLAIN EXTENDED <your_query_here> Is the table you wanted to give the DDL for builder_row or builder_column? Can you remove the incorrect one please?

    – Vérace
    Nov 3 '17 at 17:09
















0












0








0








Starting in the middle of last night (of course) I have a query that stops using an index and when that happens, it takes over an hour to complete vs. about 3 seconds when it uses the index. This query has been run for more than a year with no issues until last night.



What I have been able to figure out is that the query is using an index sometimes and not others; using explain. It has been slow for 2 hours, then fast for 1 hour and now slow again, etc.



When the query is running fast, explain tells me the query is using the key: builder_row_id



When it is running slow, it has no key and the Extra has: Using join buffer (Block Nested Loop)



Here are the 2 rows from explain, sorry about the formatting:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e8_,,ALL,builder_row_id,,,,1,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e8_,,ref,builder_row_id,builder_row_id,6,"my_db_name.e6_.builder_block_id,const",4,100,

SELECT
e0_.id AS id_0,
e0_.name AS name_1,
e0_.content_id AS content_id_2,
e0_.from_label AS from_label_3,
e0_.support_address AS support_address_4,
e0_.actual_from_label AS actual_from_label_5,
e0_.actual_from_address AS actual_from_address_6,
e0_.enable_wysiwyg AS enable_wysiwyg_7,
e0_.enable_conversation AS enable_conversation_8,
e0_.folder_id AS folder_id_9,
e0_.enable_transactional AS enable_transactional_10,
e0_.type_id AS type_id_11,
e0_.utm_content AS utm_content_12,
e1_.builder_style_id AS builder_style_id_13,
e1_.id AS id_14,
e1_.builder_style_key AS builder_style_key_15,
e1_.builder_style_value AS builder_style_value_16,
e1_.builder_style_delete_status AS builder_style_delete_status_17,
e2_.content_id AS content_id_18,
e2_.content_text AS content_text_19,
e2_.content_html AS content_html_20,
e2_.content_subject AS content_subject_21,
e2_.content_preview_png AS content_preview_png_22,
e3_.builder_region_id AS builder_region_id_23,
e3_.id AS id_24,
e3_.builder_region_name AS builder_region_name_25,
e3_.builder_region_type_id AS builder_region_type_id_26,
e3_.builder_region_delete_status AS builder_region_delete_status_27,
e3_.builder_region_sort_order AS builder_region_sort_order_28,
e3_.builder_ui_id AS builder_ui_id_29,
e4_.builder_region_style_id AS builder_region_style_id_30,
e4_.builder_region_id AS builder_region_id_31,
e4_.builder_region_style_key AS builder_region_style_key_32,
e4_.builder_region_style_value AS builder_region_style_value_33,
e4_.builder_ui_id AS builder_ui_id_34,
e4_.builder_region_style_delete_status AS builder_region_style_delete_status_35,
e5_.builder_row_id AS builder_row_id_36,
e5_.builder_region_id AS builder_region_id_37,
e5_.builder_row_type_id AS builder_row_type_id_38,
e5_.builder_row_delete_status AS builder_row_delete_status_39,
e5_.builder_row_sort_order AS builder_row_sort_order_40,
e5_.builder_ui_id AS builder_ui_id_41,
e6_.builder_block_id AS builder_block_id_42,
e6_.builder_row_id AS builder_row_id_43,
e6_.builder_block_type_id AS builder_block_type_id_44,
e6_.builder_block_delete_status AS builder_block_delete_status_45,
e6_.builder_block_sort_order AS builder_block_sort_order_46,
e6_.builder_ui_id AS builder_ui_id_47,
e7_.builder_block_attribute_id AS builder_block_attribute_id_48,
e7_.builder_block_id AS builder_block_id_49,
e7_.builder_block_attribute_key AS builder_block_attribute_key_50,
e7_.builder_block_attribute_value AS builder_block_attribute_value_51,
e7_.builder_block_attribute_delete_status AS builder_block_attribute_delete_status_52,
e8_.builder_column_id AS builder_column_id_53,
e8_.builder_block_id AS builder_block_id_54,
e8_.parent_builder_column_id AS parent_builder_column_id_55,
e8_.builder_column_type_id AS builder_column_type_id_56,
e8_.builder_column_delete_status AS builder_column_delete_status_57,
e8_.builder_column_sort_order AS builder_column_sort_order_58,
e8_.builder_ui_id AS builder_ui_id_59,
e9_.builder_column_style_id AS builder_column_style_id_60,
e9_.builder_column_style_key AS builder_column_style_key_61,
e9_.builder_column_style_value AS builder_column_style_value_62,
e9_.builder_ui_id AS builder_ui_id_63,
e9_.builder_column_style_delete_status AS builder_column_style_delete_status_64,
e10_.builder_column_attribute_id AS builder_column_attribute_id_65,
e10_.builder_column_attribute_key AS builder_column_attribute_key_66,
e10_.builder_column_attribute_value AS builder_column_attribute_value_67,
e10_.builder_ui_id AS builder_ui_id_68,
e10_.builder_column_attribute_delete_status AS builder_column_attribute_delete_status_69,
e11_.builder_column_conf_id AS builder_column_conf_id_70,
e11_.builder_column_conf_key AS builder_column_conf_key_71,
e11_.builder_column_conf_value AS builder_column_conf_value_72,
e11_.builder_ui_id AS builder_ui_id_73,
e11_.builder_column_conf_delete_status AS builder_column_conf_delete_status_74,
e12_.builder_column_id AS builder_column_id_75,
e12_.builder_block_id AS builder_block_id_76,
e12_.parent_builder_column_id AS parent_builder_column_id_77,
e12_.builder_column_type_id AS builder_column_type_id_78,
e12_.builder_column_delete_status AS builder_column_delete_status_79,
e12_.builder_column_sort_order AS builder_column_sort_order_80,
e12_.builder_ui_id AS builder_ui_id_81,
e0_.content_id AS content_id_82,
e0_.product_id AS product_id_83,
e0_.unsubscribe_message_id AS unsubscribe_message_id_84,
e0_.unsubscribe_language_id AS unsubscribe_language_id_85,
e0_.rbac_role_id AS rbac_role_id_86,
e0_.folder_id AS folder_id_87,
e1_.id AS id_88,
e3_.id AS id_89,
e4_.builder_region_id AS builder_region_id_90,
e5_.builder_region_id AS builder_region_id_91,
e6_.builder_row_id AS builder_row_id_92,
e7_.builder_block_id AS builder_block_id_93,
e8_.builder_block_id AS builder_block_id_94,
e8_.parent_builder_column_id AS parent_builder_column_id_95,
e9_.builder_column_id AS builder_column_id_96,
e10_.builder_column_id AS builder_column_id_97,
e11_.builder_column_id AS builder_column_id_98,
e12_.builder_block_id AS builder_block_id_99,
e12_.parent_builder_column_id AS parent_builder_column_id_100
FROM
email e0_
LEFT JOIN builder_style e1_ ON e0_.id = e1_.id AND (e1_.builder_style_delete_status = 0)
LEFT JOIN content e2_ ON e0_.content_id = e2_.content_id
LEFT JOIN builder_region e3_ ON e0_.id = e3_.id AND (e3_.builder_region_delete_status = 0)
LEFT JOIN builder_region_style e4_ ON e3_.builder_region_id = e4_.builder_region_id AND (e4_.builder_region_style_delete_status = 0)
LEFT JOIN builder_row e5_ ON e3_.builder_region_id = e5_.builder_region_id AND (e5_.builder_row_delete_status = 0)
LEFT JOIN builder_block e6_ ON e5_.builder_row_id = e6_.builder_row_id AND (e6_.builder_block_delete_status = 0)
LEFT JOIN builder_block_attribute e7_ ON e6_.builder_block_id = e7_.builder_block_id AND (e7_.builder_block_attribute_delete_status = 0)
LEFT JOIN builder_column e8_ ON e6_.builder_block_id = e8_.builder_block_id AND (e8_.builder_column_delete_status = 0)
LEFT JOIN builder_column_style e9_ ON e8_.builder_column_id = e9_.builder_column_id AND (e9_.builder_column_style_delete_status = 0)
LEFT JOIN builder_column_attribute e10_ ON e8_.builder_column_id = e10_.builder_column_id AND (e10_.builder_column_attribute_delete_status = 0)
LEFT JOIN builder_column_conf e11_ ON e8_.builder_column_id = e11_.builder_column_id AND (e11_.builder_column_conf_delete_status = 0) AND (e11_.builder_column_conf_delete_status = 0)
LEFT JOIN builder_column e12_ ON e8_.builder_column_id = e12_.parent_builder_column_id AND (e12_.builder_column_delete_status = 0)
WHERE
e0_.delete_status = 0
AND
e0_.product_id = xxxxx
AND
e0_.id = xxxxx
ORDER BY
e3_.builder_region_sort_order ASC,

e5_.builder_row_sort_order ASC,

e6_.builder_block_sort_order ASC,

e8_.builder_column_sort_order ASC


Can someone point me in the right direction on what is going on here?



Here is the full explain:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e0_,,const,PRIMARY,id,id_2,product_id,customer_id,delete_status,rbac_role_id,delete_status_2,PRIMARY,8,const,1,100,"Using temporary; Using filesort"
1,SIMPLE,e1_,,ref,id,id,5,const,const,3,100,Using where
1,SIMPLE,e2_,,const,PRIMARY,PRIMARY,8,const,1,100,
1,SIMPLE,e3_,,ref,delete,delete,5,const,const,4,100,Using where
1,SIMPLE,e4_,,ref,builder_region_id,builder_region_id,5,"my_db_name.e3_.builder_region_id,const',1,100,
1,SIMPLE,e5_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e3_.builder_region_id,const",1,100,
1,SIMPLE,e6_,,ref,builder_region_id,builder_region_id,6,"my_db_name.e5_.builder_row_id,const",1,100,
1,SIMPLE,e7_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e6_.builder_block_id,const",1,100,
1,SIMPLE,e8_,,ALL,builder_row_id,,,,8,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e9_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const',5,100,
1,SIMPLE,e10_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",2,100,
1,SIMPLE,e11_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",1,100,
1,SIMPLE,e12_,,ALL,child_col,,,,8,100,Using where; Using join buffer (Block Nested Loop)


I just realized I posted the wrong create table:



CREATE TABLE `builder_column` (
`builder_column_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`builder_block_id` int(11) unsigned DEFAULT NULL,
`builder_column_type_id` tinyint(3) NOT NULL,
`builder_column_delete_status` tinyint(1) unsigned NOT NULL,
`builder_column_sort_order` tinyint(3) unsigned NOT NULL,
`builder_column_create_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`builder_column_modify_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`builder_ui_id` varchar(200) DEFAULT '',
`parent_builder_column_id` int(11) unsigned DEFAULT NULL,
`builder_column_flags` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`builder_column_id`),
KEY `builder_row_id` (`builder_block_id`,`builder_column_delete_status`),
KEY `child_col` (`parent_builder_column_id`,`builder_column_delete_status`)
) ENGINE=TokuDB AUTO_INCREMENT=901184 DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_SNAPPY;


UPDATE:
I have found that if I add:



FORCE INDEX FOR JOIN (email_builder_row_id)


and



FORCE INDEX FOR JOIN (child_col)


it will use the index and returns fast.










share|improve this question
















Starting in the middle of last night (of course) I have a query that stops using an index and when that happens, it takes over an hour to complete vs. about 3 seconds when it uses the index. This query has been run for more than a year with no issues until last night.



What I have been able to figure out is that the query is using an index sometimes and not others; using explain. It has been slow for 2 hours, then fast for 1 hour and now slow again, etc.



When the query is running fast, explain tells me the query is using the key: builder_row_id



When it is running slow, it has no key and the Extra has: Using join buffer (Block Nested Loop)



Here are the 2 rows from explain, sorry about the formatting:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e8_,,ALL,builder_row_id,,,,1,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e8_,,ref,builder_row_id,builder_row_id,6,"my_db_name.e6_.builder_block_id,const",4,100,

SELECT
e0_.id AS id_0,
e0_.name AS name_1,
e0_.content_id AS content_id_2,
e0_.from_label AS from_label_3,
e0_.support_address AS support_address_4,
e0_.actual_from_label AS actual_from_label_5,
e0_.actual_from_address AS actual_from_address_6,
e0_.enable_wysiwyg AS enable_wysiwyg_7,
e0_.enable_conversation AS enable_conversation_8,
e0_.folder_id AS folder_id_9,
e0_.enable_transactional AS enable_transactional_10,
e0_.type_id AS type_id_11,
e0_.utm_content AS utm_content_12,
e1_.builder_style_id AS builder_style_id_13,
e1_.id AS id_14,
e1_.builder_style_key AS builder_style_key_15,
e1_.builder_style_value AS builder_style_value_16,
e1_.builder_style_delete_status AS builder_style_delete_status_17,
e2_.content_id AS content_id_18,
e2_.content_text AS content_text_19,
e2_.content_html AS content_html_20,
e2_.content_subject AS content_subject_21,
e2_.content_preview_png AS content_preview_png_22,
e3_.builder_region_id AS builder_region_id_23,
e3_.id AS id_24,
e3_.builder_region_name AS builder_region_name_25,
e3_.builder_region_type_id AS builder_region_type_id_26,
e3_.builder_region_delete_status AS builder_region_delete_status_27,
e3_.builder_region_sort_order AS builder_region_sort_order_28,
e3_.builder_ui_id AS builder_ui_id_29,
e4_.builder_region_style_id AS builder_region_style_id_30,
e4_.builder_region_id AS builder_region_id_31,
e4_.builder_region_style_key AS builder_region_style_key_32,
e4_.builder_region_style_value AS builder_region_style_value_33,
e4_.builder_ui_id AS builder_ui_id_34,
e4_.builder_region_style_delete_status AS builder_region_style_delete_status_35,
e5_.builder_row_id AS builder_row_id_36,
e5_.builder_region_id AS builder_region_id_37,
e5_.builder_row_type_id AS builder_row_type_id_38,
e5_.builder_row_delete_status AS builder_row_delete_status_39,
e5_.builder_row_sort_order AS builder_row_sort_order_40,
e5_.builder_ui_id AS builder_ui_id_41,
e6_.builder_block_id AS builder_block_id_42,
e6_.builder_row_id AS builder_row_id_43,
e6_.builder_block_type_id AS builder_block_type_id_44,
e6_.builder_block_delete_status AS builder_block_delete_status_45,
e6_.builder_block_sort_order AS builder_block_sort_order_46,
e6_.builder_ui_id AS builder_ui_id_47,
e7_.builder_block_attribute_id AS builder_block_attribute_id_48,
e7_.builder_block_id AS builder_block_id_49,
e7_.builder_block_attribute_key AS builder_block_attribute_key_50,
e7_.builder_block_attribute_value AS builder_block_attribute_value_51,
e7_.builder_block_attribute_delete_status AS builder_block_attribute_delete_status_52,
e8_.builder_column_id AS builder_column_id_53,
e8_.builder_block_id AS builder_block_id_54,
e8_.parent_builder_column_id AS parent_builder_column_id_55,
e8_.builder_column_type_id AS builder_column_type_id_56,
e8_.builder_column_delete_status AS builder_column_delete_status_57,
e8_.builder_column_sort_order AS builder_column_sort_order_58,
e8_.builder_ui_id AS builder_ui_id_59,
e9_.builder_column_style_id AS builder_column_style_id_60,
e9_.builder_column_style_key AS builder_column_style_key_61,
e9_.builder_column_style_value AS builder_column_style_value_62,
e9_.builder_ui_id AS builder_ui_id_63,
e9_.builder_column_style_delete_status AS builder_column_style_delete_status_64,
e10_.builder_column_attribute_id AS builder_column_attribute_id_65,
e10_.builder_column_attribute_key AS builder_column_attribute_key_66,
e10_.builder_column_attribute_value AS builder_column_attribute_value_67,
e10_.builder_ui_id AS builder_ui_id_68,
e10_.builder_column_attribute_delete_status AS builder_column_attribute_delete_status_69,
e11_.builder_column_conf_id AS builder_column_conf_id_70,
e11_.builder_column_conf_key AS builder_column_conf_key_71,
e11_.builder_column_conf_value AS builder_column_conf_value_72,
e11_.builder_ui_id AS builder_ui_id_73,
e11_.builder_column_conf_delete_status AS builder_column_conf_delete_status_74,
e12_.builder_column_id AS builder_column_id_75,
e12_.builder_block_id AS builder_block_id_76,
e12_.parent_builder_column_id AS parent_builder_column_id_77,
e12_.builder_column_type_id AS builder_column_type_id_78,
e12_.builder_column_delete_status AS builder_column_delete_status_79,
e12_.builder_column_sort_order AS builder_column_sort_order_80,
e12_.builder_ui_id AS builder_ui_id_81,
e0_.content_id AS content_id_82,
e0_.product_id AS product_id_83,
e0_.unsubscribe_message_id AS unsubscribe_message_id_84,
e0_.unsubscribe_language_id AS unsubscribe_language_id_85,
e0_.rbac_role_id AS rbac_role_id_86,
e0_.folder_id AS folder_id_87,
e1_.id AS id_88,
e3_.id AS id_89,
e4_.builder_region_id AS builder_region_id_90,
e5_.builder_region_id AS builder_region_id_91,
e6_.builder_row_id AS builder_row_id_92,
e7_.builder_block_id AS builder_block_id_93,
e8_.builder_block_id AS builder_block_id_94,
e8_.parent_builder_column_id AS parent_builder_column_id_95,
e9_.builder_column_id AS builder_column_id_96,
e10_.builder_column_id AS builder_column_id_97,
e11_.builder_column_id AS builder_column_id_98,
e12_.builder_block_id AS builder_block_id_99,
e12_.parent_builder_column_id AS parent_builder_column_id_100
FROM
email e0_
LEFT JOIN builder_style e1_ ON e0_.id = e1_.id AND (e1_.builder_style_delete_status = 0)
LEFT JOIN content e2_ ON e0_.content_id = e2_.content_id
LEFT JOIN builder_region e3_ ON e0_.id = e3_.id AND (e3_.builder_region_delete_status = 0)
LEFT JOIN builder_region_style e4_ ON e3_.builder_region_id = e4_.builder_region_id AND (e4_.builder_region_style_delete_status = 0)
LEFT JOIN builder_row e5_ ON e3_.builder_region_id = e5_.builder_region_id AND (e5_.builder_row_delete_status = 0)
LEFT JOIN builder_block e6_ ON e5_.builder_row_id = e6_.builder_row_id AND (e6_.builder_block_delete_status = 0)
LEFT JOIN builder_block_attribute e7_ ON e6_.builder_block_id = e7_.builder_block_id AND (e7_.builder_block_attribute_delete_status = 0)
LEFT JOIN builder_column e8_ ON e6_.builder_block_id = e8_.builder_block_id AND (e8_.builder_column_delete_status = 0)
LEFT JOIN builder_column_style e9_ ON e8_.builder_column_id = e9_.builder_column_id AND (e9_.builder_column_style_delete_status = 0)
LEFT JOIN builder_column_attribute e10_ ON e8_.builder_column_id = e10_.builder_column_id AND (e10_.builder_column_attribute_delete_status = 0)
LEFT JOIN builder_column_conf e11_ ON e8_.builder_column_id = e11_.builder_column_id AND (e11_.builder_column_conf_delete_status = 0) AND (e11_.builder_column_conf_delete_status = 0)
LEFT JOIN builder_column e12_ ON e8_.builder_column_id = e12_.parent_builder_column_id AND (e12_.builder_column_delete_status = 0)
WHERE
e0_.delete_status = 0
AND
e0_.product_id = xxxxx
AND
e0_.id = xxxxx
ORDER BY
e3_.builder_region_sort_order ASC,

e5_.builder_row_sort_order ASC,

e6_.builder_block_sort_order ASC,

e8_.builder_column_sort_order ASC


Can someone point me in the right direction on what is going on here?



Here is the full explain:



id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,e0_,,const,PRIMARY,id,id_2,product_id,customer_id,delete_status,rbac_role_id,delete_status_2,PRIMARY,8,const,1,100,"Using temporary; Using filesort"
1,SIMPLE,e1_,,ref,id,id,5,const,const,3,100,Using where
1,SIMPLE,e2_,,const,PRIMARY,PRIMARY,8,const,1,100,
1,SIMPLE,e3_,,ref,delete,delete,5,const,const,4,100,Using where
1,SIMPLE,e4_,,ref,builder_region_id,builder_region_id,5,"my_db_name.e3_.builder_region_id,const',1,100,
1,SIMPLE,e5_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e3_.builder_region_id,const",1,100,
1,SIMPLE,e6_,,ref,builder_region_id,builder_region_id,6,"my_db_name.e5_.builder_row_id,const",1,100,
1,SIMPLE,e7_,,ref,builder_block_id,builder_block_id,5,"my_db_name.e6_.builder_block_id,const",1,100,
1,SIMPLE,e8_,,ALL,builder_row_id,,,,8,100,Using where; Using join buffer (Block Nested Loop)
1,SIMPLE,e9_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const',5,100,
1,SIMPLE,e10_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",2,100,
1,SIMPLE,e11_,,ref,builder_column_id,builder_column_id,5,"my_db_name.e8_.builder_column_id,const",1,100,
1,SIMPLE,e12_,,ALL,child_col,,,,8,100,Using where; Using join buffer (Block Nested Loop)


I just realized I posted the wrong create table:



CREATE TABLE `builder_column` (
`builder_column_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`builder_block_id` int(11) unsigned DEFAULT NULL,
`builder_column_type_id` tinyint(3) NOT NULL,
`builder_column_delete_status` tinyint(1) unsigned NOT NULL,
`builder_column_sort_order` tinyint(3) unsigned NOT NULL,
`builder_column_create_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`builder_column_modify_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`builder_ui_id` varchar(200) DEFAULT '',
`parent_builder_column_id` int(11) unsigned DEFAULT NULL,
`builder_column_flags` tinyint(3) unsigned DEFAULT '0',
PRIMARY KEY (`builder_column_id`),
KEY `builder_row_id` (`builder_block_id`,`builder_column_delete_status`),
KEY `child_col` (`parent_builder_column_id`,`builder_column_delete_status`)
) ENGINE=TokuDB AUTO_INCREMENT=901184 DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_SNAPPY;


UPDATE:
I have found that if I add:



FORCE INDEX FOR JOIN (email_builder_row_id)


and



FORCE INDEX FOR JOIN (child_col)


it will use the index and returns fast.







mysql query-performance tokudb






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 3 '17 at 22:09









Rick James

43.7k22259




43.7k22259










asked Nov 3 '17 at 15:30









Jeff WardJeff Ward

11




11





bumped to the homepage by Community 5 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 5 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.










  • 1





    Please add the CREATE TABLE and the query command.

    – McNets
    Nov 3 '17 at 15:32











  • And do you also need the create tables from the other joined tables?

    – Jeff Ward
    Nov 3 '17 at 15:40











  • Ppl must know why sometimes index is not used.

    – McNets
    Nov 3 '17 at 15:41











  • Please edit this sort of information into the question; it allows you to include formatting, is much easier to read, and doesn't have a limit on how much you can include. Thanks!

    – RDFozz
    Nov 3 '17 at 15:43











  • I'm going to suggest that your data model is seriously flawed! You have table numbers going from 0 - 12 and column numbers from 1 - 100. Any time names have numbers after them like that, it's indicative of a big problem. It looks like just one horrendous mess! Please post the results of EXPLAIN EXTENDED <your_query_here> Is the table you wanted to give the DDL for builder_row or builder_column? Can you remove the incorrect one please?

    – Vérace
    Nov 3 '17 at 17:09
















  • 1





    Please add the CREATE TABLE and the query command.

    – McNets
    Nov 3 '17 at 15:32











  • And do you also need the create tables from the other joined tables?

    – Jeff Ward
    Nov 3 '17 at 15:40











  • Ppl must know why sometimes index is not used.

    – McNets
    Nov 3 '17 at 15:41











  • Please edit this sort of information into the question; it allows you to include formatting, is much easier to read, and doesn't have a limit on how much you can include. Thanks!

    – RDFozz
    Nov 3 '17 at 15:43











  • I'm going to suggest that your data model is seriously flawed! You have table numbers going from 0 - 12 and column numbers from 1 - 100. Any time names have numbers after them like that, it's indicative of a big problem. It looks like just one horrendous mess! Please post the results of EXPLAIN EXTENDED <your_query_here> Is the table you wanted to give the DDL for builder_row or builder_column? Can you remove the incorrect one please?

    – Vérace
    Nov 3 '17 at 17:09










1




1





Please add the CREATE TABLE and the query command.

– McNets
Nov 3 '17 at 15:32





Please add the CREATE TABLE and the query command.

– McNets
Nov 3 '17 at 15:32













And do you also need the create tables from the other joined tables?

– Jeff Ward
Nov 3 '17 at 15:40





And do you also need the create tables from the other joined tables?

– Jeff Ward
Nov 3 '17 at 15:40













Ppl must know why sometimes index is not used.

– McNets
Nov 3 '17 at 15:41





Ppl must know why sometimes index is not used.

– McNets
Nov 3 '17 at 15:41













Please edit this sort of information into the question; it allows you to include formatting, is much easier to read, and doesn't have a limit on how much you can include. Thanks!

– RDFozz
Nov 3 '17 at 15:43





Please edit this sort of information into the question; it allows you to include formatting, is much easier to read, and doesn't have a limit on how much you can include. Thanks!

– RDFozz
Nov 3 '17 at 15:43













I'm going to suggest that your data model is seriously flawed! You have table numbers going from 0 - 12 and column numbers from 1 - 100. Any time names have numbers after them like that, it's indicative of a big problem. It looks like just one horrendous mess! Please post the results of EXPLAIN EXTENDED <your_query_here> Is the table you wanted to give the DDL for builder_row or builder_column? Can you remove the incorrect one please?

– Vérace
Nov 3 '17 at 17:09







I'm going to suggest that your data model is seriously flawed! You have table numbers going from 0 - 12 and column numbers from 1 - 100. Any time names have numbers after them like that, it's indicative of a big problem. It looks like just one horrendous mess! Please post the results of EXPLAIN EXTENDED <your_query_here> Is the table you wanted to give the DDL for builder_row or builder_column? Can you remove the incorrect one please?

– Vérace
Nov 3 '17 at 17:09












1 Answer
1






active

oldest

votes


















0














(OK, not yet a 'real' Answer, but here goes...)



I would be filthy rich if I had a bitcoin for every "Why is it not using my index" Questions that I have answered. This forum is loaded with such; so is stackoverflow.com .



But, alas, there is no single Answer that fits all Questions. So, I will stare at your case. Ouch, I may not be able to answer yours since it involves Tokudb. (I added a tag, so you might get more traffic.)



Which table is not using the index? Where are the other SHOW CREATE TABLEs? The Optimizer is free to rearrange the order of hitting the tables; this could be part of the problem.



Have you ANALYZEd the tables recently? (I don't know how much this is needed in TokuDB.)



Don't use LEFT JOIN unless the 'right' table is really optional. Switching to JOIN gives the Optimizer more options.



Let's see the EXPLAIN before and after the change in query plan.



Is id the PRIMARY KEY of email? If so, why filter on product_id and delete_status if you have the id?






share|improve this answer


























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f190060%2fquery-sometime-not-using-index%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    (OK, not yet a 'real' Answer, but here goes...)



    I would be filthy rich if I had a bitcoin for every "Why is it not using my index" Questions that I have answered. This forum is loaded with such; so is stackoverflow.com .



    But, alas, there is no single Answer that fits all Questions. So, I will stare at your case. Ouch, I may not be able to answer yours since it involves Tokudb. (I added a tag, so you might get more traffic.)



    Which table is not using the index? Where are the other SHOW CREATE TABLEs? The Optimizer is free to rearrange the order of hitting the tables; this could be part of the problem.



    Have you ANALYZEd the tables recently? (I don't know how much this is needed in TokuDB.)



    Don't use LEFT JOIN unless the 'right' table is really optional. Switching to JOIN gives the Optimizer more options.



    Let's see the EXPLAIN before and after the change in query plan.



    Is id the PRIMARY KEY of email? If so, why filter on product_id and delete_status if you have the id?






    share|improve this answer






























      0














      (OK, not yet a 'real' Answer, but here goes...)



      I would be filthy rich if I had a bitcoin for every "Why is it not using my index" Questions that I have answered. This forum is loaded with such; so is stackoverflow.com .



      But, alas, there is no single Answer that fits all Questions. So, I will stare at your case. Ouch, I may not be able to answer yours since it involves Tokudb. (I added a tag, so you might get more traffic.)



      Which table is not using the index? Where are the other SHOW CREATE TABLEs? The Optimizer is free to rearrange the order of hitting the tables; this could be part of the problem.



      Have you ANALYZEd the tables recently? (I don't know how much this is needed in TokuDB.)



      Don't use LEFT JOIN unless the 'right' table is really optional. Switching to JOIN gives the Optimizer more options.



      Let's see the EXPLAIN before and after the change in query plan.



      Is id the PRIMARY KEY of email? If so, why filter on product_id and delete_status if you have the id?






      share|improve this answer




























        0












        0








        0







        (OK, not yet a 'real' Answer, but here goes...)



        I would be filthy rich if I had a bitcoin for every "Why is it not using my index" Questions that I have answered. This forum is loaded with such; so is stackoverflow.com .



        But, alas, there is no single Answer that fits all Questions. So, I will stare at your case. Ouch, I may not be able to answer yours since it involves Tokudb. (I added a tag, so you might get more traffic.)



        Which table is not using the index? Where are the other SHOW CREATE TABLEs? The Optimizer is free to rearrange the order of hitting the tables; this could be part of the problem.



        Have you ANALYZEd the tables recently? (I don't know how much this is needed in TokuDB.)



        Don't use LEFT JOIN unless the 'right' table is really optional. Switching to JOIN gives the Optimizer more options.



        Let's see the EXPLAIN before and after the change in query plan.



        Is id the PRIMARY KEY of email? If so, why filter on product_id and delete_status if you have the id?






        share|improve this answer















        (OK, not yet a 'real' Answer, but here goes...)



        I would be filthy rich if I had a bitcoin for every "Why is it not using my index" Questions that I have answered. This forum is loaded with such; so is stackoverflow.com .



        But, alas, there is no single Answer that fits all Questions. So, I will stare at your case. Ouch, I may not be able to answer yours since it involves Tokudb. (I added a tag, so you might get more traffic.)



        Which table is not using the index? Where are the other SHOW CREATE TABLEs? The Optimizer is free to rearrange the order of hitting the tables; this could be part of the problem.



        Have you ANALYZEd the tables recently? (I don't know how much this is needed in TokuDB.)



        Don't use LEFT JOIN unless the 'right' table is really optional. Switching to JOIN gives the Optimizer more options.



        Let's see the EXPLAIN before and after the change in query plan.



        Is id the PRIMARY KEY of email? If so, why filter on product_id and delete_status if you have the id?







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 4 '17 at 1:56

























        answered Nov 3 '17 at 22:13









        Rick JamesRick James

        43.7k22259




        43.7k22259






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f190060%2fquery-sometime-not-using-index%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh