mysql fix Using where;

问题:

My SQL Query:

SELECT *
FROM updates_cats
WHERE uid =118697835834
ORDER BY created_date ASC

Current Indexes:

index1(uid, created_date)

EXPLAIN EXTENDED result:

1 SIMPLE updates_cats ref index1 index1 8 const 2 100.00 Using where

How can i fix the Extra field where it has Using where so it can use the indexes instead?

EDIT: 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

回答1:

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.

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.

Here are things that you should be concerned about:

• Using filesort
• Using temporary
• Not using an index: NULL in the 'key' column of the EXPLAIN and a large number of rows in the 'rows' column.

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