mysql fix Using where;
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 theEXPLAIN
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
stergosz
Joomla & WordPress developer based in Greece Developing Joomla extensions on tassos.gr Developing WordPress plugins on fireplugins.com
Updated on July 09, 2022Comments
-
stergosz almost 2 years
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
-
stergosz about 12 yearssince 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?
-
Marcus Adams about 12 yearsTry it with just
uid
andcreated_date
. YourEXPLAIN
will showUsing 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 thetext
field, you can't use a covering index anyway, so just stick with what you've got. Read about "covering indexes". -
stergosz about 12 yearsjust tried it but it will return php error saying that one variable tried to get a column not specified in the SELECT...
-
Marcus Adams about 12 yearsI 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.