CODEWITHSUNDEEP
mysqlsqloptimization
AitorF
AitorF

Reputation: 1390

Why is MySQL "using_where" with this query?

I have this query:

SELECT *
FROM items i
WHERE i.user_id = 1 AND i.source_id = 34
ORDER BY i.time DESC LIMIT 0, 30;

And this composite index:

idx_user_src_time (user_id, source_id, time)

Explain shows that MySQL is indeed using the index:

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'ui', 'ref', 'PRIMARY,idx_source_id,idx_user_src_time', 'idx_user_src_time', '8', 'const,const', '329', '100.00', 'Using where'

But in the extra column says he needs to perform some where.

Could somebody explain me why MySQL needs to perform additional stuff with this query and doesn't have enough with the index???

EDIT

If I remove the order by clause, the EXPLAIN remains the same (uses the same index) but the using_where dissapears.

EDIT

The table looks like:

CREATE TABLE `items` (`user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `source_id` int(11) NOT NULL,
  `time` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`user_id`,`item_id`)
  KEY `idx_iid_user_src_time` (`item_id`,`user_id`,`source_id`,`time`) USING BTREE,
  KEY `idx_user_time` (`user_id`,`time_order`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Upvotes: 1

Views: 116

Answers (1)

Saurabh
Saurabh

Reputation: 73649

From Mysql doc:

A WHERE clause is used to restrict which rows to match against the next table or send to the client. 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.

Upvotes: 2

Related Questions