Sfrgttk

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.

(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?