Doubt about Explain

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!

Seems like a bug to me.

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

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! )

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

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! )

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.

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!

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.”