•  

Announcement

Announcement Module
Collapse
No announcement yet.

Doubt about Explain

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

  • Doubt about Explain

    #1
    hi all:

    ) After reading the book ,i have a good understanding of Using Explain to see the execution plan of mysql,except for the meaning of "using where " appeared at "Extra" sometimes.

    eg. for the information below:

    CREATE TABLE `newsfeeds` (
    `id` bigint(20) NOT NULL,
    `user_id` bigint(20) NOT NULL,
    `source_id` bigint(20) NOT NULL,
    `target_ids` varchar(1000) DEFAULT NULL,
    `template_bundle_id` bigint(20) DEFAULT NULL,
    `template_data` varchar(4000) DEFAULT NULL,
    `app_id` bigint(20) DEFAULT NULL,
    `app_item_id` varchar(50) DEFAULT NULL,
    `created_at` bigint(20) NOT NULL,
    `updated_at` bigint(20) DEFAULT NULL,
    `splittime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
    `star` bit(1) DEFAULT NULL,
    `from_type` tinyint(4) DEFAULT '0',
    PRIMARY KEY (`id`,`splittime`),
    KEY `idx_userid_createat` (`user_id`,`created_at`),
    KEY `idx_userid_star_createat` (`user_id`,`star`,`created_at`),
    KEY `idx_splittime` (`splittime`),
    KEY `idx_userid_appid_createat` (`user_id`,`app_id`,`created_at`),
    KEY `idx_userid_tempbd_createdat` (`user_id`,`template_bundle_id`,`created_at`),
    KEY `idx_userid` (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    mysql> explain extended select id from newsfeeds where id<100000 \G;
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: newsfeeds
    type: range
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 8
    ref: NULL
    rows: 12
    filtered: 100.00
    Extra: Using where; Using index
    1 row in set, 1 warning (0.00 sec)

    As the book says ,[using where] means mysql will post-filters
    rows from storage engine,but since id can be get through covering index and with no need to read rows again,i have no idea why it shows here!

    The question has puzzle me for a very long time,so I'm urgent for your answer!

    thanks a million!
    Tags: None

  • #2
    Seems like a bug to me.

    http://bugs.mysql.com/bug.php?id=30733

    Comment


    • #3
      thank you very much for your quick reply !But it seems something different from the bug in http://bugs.mysql.com/bug.php?id=30733.
      As my mysql circumstance is:
      mysql> select version();
      +------------+
      | version() |
      +------------+
      | 5.1.34-log |
      +------------+
      1 row in set (0.00 sec)

      and the problem in the bug report looks good here!
      mysql> explain select id from newsfeeds where id=105018 \G;
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: newsfeeds
      type: ref
      possible_keys: PRIMARY
      key: PRIMARY
      key_len: 8
      ref: const
      rows: 12
      Extra: Using index
      1 row in set (0.00 sec)

      ERROR:
      No query specified

      but

      mysql> explain select id from newsfeeds where id<105018 \G;
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: newsfeeds
      type: range
      possible_keys: PRIMARY
      key: PRIMARY
      key_len: 8
      ref: NULL
      rows: 12
      Extra: Using where; Using index
      1 row in set (0.00 sec)

      ERROR:
      No query specified

      I think both '=' and '<' can retrieve data through index but not reading row! Does it readlly a bug,or some mechanism i don't know.

      since "using where" appear at so many place i can explain .eg in the article :http://www.mysqlperformanceblog.com/2007/04/10/count-vs-cou ntcol/

      #
      mysql> EXPLAIN SELECT count(*) FROM fact WHERE i<10000 \G
      #
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      TABLE: fact
      type: range
      possible_keys: i
      KEY: i
      key_len: 4
      ref: NULL
      rows: 691619
      Extra: USING WHERE; USING INDEX
      1 row IN SET (0.00 sec)


      mysql> EXPLAIN SELECT count(val2) FROM fact WHERE i<10000 \G
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      TABLE: fact
      type: range
      possible_keys: i
      KEY: i
      key_len: 4
      ref: NULL
      rows: 691619
      Extra: USING WHERE

      would you mind tell me something about what mysql real does with such queies,and does "using where " here is really explain something or else!

      Thanks again! )

      Comment


      • #4
        Still a bug; http://ritmark.com/?p=24

        Comment


        • #5
          Thanks a lot for your kind help!
          but there is still some questions about [using index] here!
          As for my examples:

          mysql> explain select id from newsfeeds where id<105018 \G;
          *************************** 1. row ***************************
          id: 1
          select_type: SIMPLE
          table: newsfeeds
          type: range
          possible_keys: PRIMARY
          key: PRIMARY
          key_len: 8
          ref: NULL
          rows: 12
          Extra: Using where; Using index
          1 row in set (0.00 sec)

          since all the column related with this query is just [id],it is obvious the query conform to the condition using [covering index].
          since covering index used by mysql is to avoid acceing the table,and it returns just the [column data],so there is no rows returned to post-filter.is it true?

          for the article you recommended:

          mysql> explain select * from t1 where a>1 \G
          *************************** 1. row ***********
          id: 1
          select_type: SIMPLE
          table: t1
          type: range
          possible_keys: PRIMARY
          key: PRIMARY
          key_len: 4
          ref: NULL
          rows: 200
          Extra: Using where; Using index
          1 row in set (0.03 sec)

          Since it used '*' to retrieve all columns and it is clear that it have to read the rows,so why "using index" show up here?

          Thanks again! )

          Comment


          • #6
            >> since covering index used by mysql is to avoid acceing the table,and it returns just the [column data],so there is no rows returned to post-filter.is it true?

            It returns just 'id', and yes it is weird that it still shows 'using where'. But since database servers usually have plenty of unused cpu capacity, I would not be too worried about this.

            >> Since it used '*' to retrieve all columns and it is clear that it have to read the rows,so why "using index" show up here?

            In his example the primary key covers all columns.

            Comment


            • #7
              So many thanks for your help,but i still have a littel question! )
              Since i was always told that [Explain] is not so reliable and it doesn't always tell the truth!what [Explain] shows is just for reference and [MYSQL] not always execute the query according to the plan what [explain] shows!So i want to know,for what you said
              >>It returns just 'id', and yes it is weird that it still shows 'using where'. But since database servers usually have plenty of unused cpu capacity, I would not be too worried about this.

              does it mean that [explain] show something wrong,or [explain] tell the truth and is just a weird action of [MYSQL]

              Thanks again and Have a good day!

              Comment


              • #8
                >> does it mean that [explain] show something wrong,or [explain] tell the truth and is just a weird action of [MYSQL]

                I haven't verified this, but the author of the second article states "I stepped through the query execution code and I can confirm that it is really performed for the first query."

                Comment

                Working...
                X