mysql修复使用where; [英] mysql fix Using where;
问题描述
我的SQL查询:
SELECT *
FROM updates_cats
WHERE uid =118697835834
ORDER BY created_date ASC
当前指数:
index1(uid, created_date)
EXPLAIN EXTENDED结果:
EXPLAIN EXTENDED result:
1 SIMPLE updates_cats ref index1 index1 8 const 2 100.00 Using where
如何修复Extra字段使用哪里可以使用索引?
How can i fix the Extra field where it has Using where so it can use the indexes instead?
编辑:SHOW CREATE TABLE:
SHOW CREATE TABLE:
CREATE TABLE `updates_cats` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`u_cat_id` bigint(20) NOT NULL DEFAULT '0',
`uid` bigint(20) NOT NULL,
`u_cat_name` text COLLATE utf8_unicode_ci NOT NULL,
`total_updates` int(11) unsigned NOT NULL DEFAULT '0',
`created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `index1` (`uid`,`created_date`)
) ENGINE=MyISAM AUTO_INCREMENT=23522 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
推荐答案
唯一比更好的东西
使用使用where;使用索引
和覆盖索引。尝试只选择 uid
和 created_date
。
The only thing that would be better than Using where
is Using where; Using index
with a "covering index". Try selecting just uid
and created_date
.
使用
的地方没问题。这意味着它将指示的索引应用于 WHERE
子句并减少返回的行数。要摆脱它,你必须摆脱 WHERE
子句。
Using where
is fine. It means it's applying the indicated index to the WHERE
clause and reducing the rows returned. To get rid of it, you'd have to get rid of the WHERE
clause.
以下是你应该关注:
-
使用filesort
-
使用临时
- 不使用索引:
NULL
inEXPLAIN
的'key'列和'rows'列中的大量行。
Using filesort
Using temporary
- Not using an index:
NULL
in the 'key' column of theEXPLAIN
and a large number of rows in the 'rows' column.
您的 EXPLAIN
结果显示MySQL正在将 index1
应用于 WHERE
子句并返回2行:
Your EXPLAIN
result shows that MySQL is applying index1
to the WHERE
clause and returning 2 rows:
1 SIMPLE updates_cats ref index1 index1 8 const 2 100.00 Using where
这篇关于mysql修复使用where;的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!