Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

MFR_MESSAGE_DELETED_I causes bad performance

Description

Recently it was seen that MySQL was selecting a bad query plan that involved the use of the following index:
MFR_MESSAGE_DELETED_I

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

areaimpl3_

ref

PRIMARY,CONTEXT_ID,MFR_AREA_CONTEXT_I

MFR_AREA_CONTEXT_I

767

cons

2

Using where; Using index

1

SIMPLE

openforumi2_

ref

PRIMARY,FKC17608478B5E2A2F,MFR_OF_PAR_BASEFRM

FKC17608478B5E2A2F

9

pc.areaimpl3_.ID

1

Using index

1

SIMPLE

messageimp0_

ref

MFR_MESSAGE_DELETED_I

MFR_MESSAGE_DELETED_I

1

const

104886

Using where

1

SIMPLE

topicimpl1_

eq_ref

PRIMARY,FK863DC0BE74C7E92B,MFR_TOPIC_PAR_I1

PRIMARY

8

pc.messageimp0_.surrogateKey

1

Using where

 

this query took more than 6 seconds to run

select count(*) as col_0_0_ from MFR_MESSAGE_T messageimp0_ inner join MFR_TOPIC_T topicimpl1_ on messageimp0_.surrogateKey=topicimpl1_.ID inner join MFR_OPEN_FORUM_T openforumi2_ on topicimpl1_.of_surrogateKey=openforumi2_.ID inner join MFR_AREA_T areaimpl3_ on openforumi2_.surrogateKey=areaimpl3_.ID where areaimpl3_.CONTEXT_ID='525e4dfd-b3e3-4b35-8988-0de3645330f5' and messageimp0_.DRAFT=0 and messageimp0_.DELETED=0 and messageimp0_.CREATED_BY='1dfa1ae4-d1c1-4c5b-bc26-e6a74b6598a2';

The cardinality on a bit(1) field is horrible and should never be used

Activity

Show:

Hudson CI Server November 12, 2015 at 8:15 PM

SUCCESS: Integrated in sakai-10-java-1.7 #240 (See http://builds.sakaiproject.org:8080/job/sakai-10-java-1.7/240/)
https://sakaiproject.atlassian.net/browse/SAK-29571#icft=SAK-29571 merge PR 728 from git (enietzel@anisakai.com: rev 321795)

Matthew Buckett November 9, 2015 at 7:28 AM

I should add we are on MySQL 5.5, I don't know if the optimizer is better under 5.6/5.7 and so only some deployments will notice this.

Matthew Buckett November 9, 2015 at 6:08 AM

+----+-------------+--------------+------+---------------------------------------------------------------------+------------------------------+---------+-------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------------------------------------------------------------+------------------------------+---------+-------------------------------+------+--------------------------+ | 1 | SIMPLE | areaimpl3_ | ref | PRIMARY,MFR_AREA_CONTEXT_UUID_UNIQUE,MFR_AREA_CONTEXT_I | MFR_AREA_CONTEXT_UUID_UNIQUE | 767 | const | 1 | Using where; Using index | | 1 | SIMPLE | openforumi2_ | ref | PRIMARY,FKC17608478B5E2A2F,MFR_OF_PARENT_BASEFORUM_I | FKC17608478B5E2A2F | 9 | sakai_staging.areaimpl3_.ID | 1 | Using where; Using index | | 1 | SIMPLE | topicimpl1_ | ref | PRIMARY,FK863DC0BE74C7E92B,MFR_TOPIC_PARENT_I1 | MFR_TOPIC_PARENT_I1 | 9 | sakai_staging.openforumi2_.ID | 18 | Using where; Using index | | 1 | SIMPLE | messageimp0_ | ref | FK80C1A316A2D0BE7B,MFR_MESSAGE_DELETED_I,MFR_MESSAGE_PARENT_TOPIC_I | FK80C1A316A2D0BE7B | 9 | sakai_staging.topicimpl1_.ID | 8 | Using where | +----+-------------+--------------+------+---------------------------------------------------------------------+------------------------------+---------+-------------------------------+------+--------------------------+

Matthew Buckett November 9, 2015 at 6:08 AM

Locally we have one of our MySQL servers using this really poor plan and another using a different one. Sadly it's the production service that's being dumb probably because of the usage patterns affecting the query planner in a bad way.

Fixed

Details

Priority

Affects versions

Fix versions

Components

Assignee

Reporter

Created June 23, 2015 at 6:40 PM
Updated January 12, 2016 at 10:03 AM
Resolved June 24, 2015 at 3:32 AM

Flag notifications