0

EXPLAIN SELECT results for some tables has:

             type   possible_keys   key       Extra
table1       ref    fl              fl        Using where
table2       ref    PRIMARY,variant variant   Using where; Using index

If would like to clarify that both tables uses index by the 'key' column, however i cannot see 'Using index' for the table1??? Should I care about this?

Ken Tang
  • 519
  • 3
  • 7
  • 20
  • 1
    I'm curious: what is the meaning of multiple question marks? I only ever learnt about single ones. – eggyal Sep 17 '12 at 06:19

2 Answers2

1

As documented under EXPLAIN Output Format:

EXPLAIN Extra Information

The Extra column of EXPLAIN output contains additional information about how MySQL resolves the query. The following list explains the values that can appear in this column.

[ deletia ]

  • Using index

    The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

If Using index is not present, an index may still be used to locate records but then MySQL may be fetching the full record to retrieve column data.

eggyal
  • 122,705
  • 18
  • 212
  • 237
0

EXPLAIN EXTENDED may be helpful if you compare its output to the filtering criteria in your query: the number of rows etc.: EXPLAIN EXTENDED Output Format

full.stack.ex
  • 1,747
  • 2
  • 11
  • 13
  • hi full.stack.ex, i tried with EXPLAIN EXTENDED it just adds to show 1 more column named Filtered that my all tables has 100.00 value for this query is it ok? – Ken Tang Sep 17 '12 at 07:06
  • Here's what they write about Using where here: [link](http://dev.mysql.com/doc/refman/5.0/en/explain-output.html#explain-extra-information), " Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL." Looks like if you have "select *" there, the DB scans all the rows. – full.stack.ex Sep 17 '12 at 09:25