mysql fix Using where;

Question

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

@fxuser: How many rows does the table have? If they are too few, then tb eindex may not be sued. — ypercubeᵀᴹ, Mar 02 '12 at 13:46
@ypercube Showing rows 0 - 29 ( 23,521 total, Query took 0.0005 sec) — stergosz, Mar 02 '12 at 13:49
Can you tell us what `SELECT COUNT(*) FROM updates_cats` and `SELECT COUNT(*) FROM updates_cats WHERE uid =118697835834` show? — ypercubeᵀᴹ, Mar 02 '12 at 13:55
i have also added the SHOW CREATE TABLE - first row shows : 23521 - second row shows: 2 — stergosz, Mar 02 '12 at 14:00
I am not sure if Using where; that it returns on EXPLAIN is a problem or not... should i optimize it to remove the Using where; on Extra field or its fine? — stergosz, Mar 02 '12 at 14:19

Marcus Adams · Accepted Answer · 2012-03-02T14:47:17.960

52

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

edited Mar 02 '12 at 14:47

answered Mar 02 '12 at 14:30

Marcus Adams

53,009
9
91
143

since i used SELECT * it means that i want most(if not all) the columns of the table... so what will help if i SELECT uid,created_date only? – stergosz Mar 02 '12 at 14:46
1

Try it with just `uid` and `created_date`. Your `EXPLAIN` will show `Using index`, which means that MySQL is able to return the entire result set directly from the index without having to visit the actual table data. This prevents two seeks, one for each row returned. If you're selecting the `text` field, you can't use a covering index anyway, so just stick with what you've got. Read about "[covering indexes](http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/)". – Marcus Adams Mar 02 '12 at 14:53
just tried it but it will return php error saying that one variable tried to get a column not specified in the SELECT... – stergosz Mar 02 '12 at 15:13
1

I was suggesting running it with `EXPLAIN` to see the difference, not changing it in your application. You can't fetch values in PHP from columns that you didn't return. – Marcus Adams Mar 02 '12 at 15:23

mysql fix Using where;

1 Answers1

Linked