Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools Database Foundations Securing and Maintaining a MySQL Database Indexing Columns in MySQL

Kyle Robertson
Kyle Robertson
31,600 Points

MySQL EXPLAIN statement still says "Using where" in the Extra column after creating an index.

Hey guys, I'm having trouble understanding why the "Extra" column from the EXPLAIN statement still says "Using where" after I created an index on the users table provided with the lesson.

Here's the code I used to create the index:

CREATE INDEX last_name_idx ON users(last_name);

And here's the code I used to query the table:

EXPLAIN SELECT * FROM users WHERE last_name = "Chalkley";

It lists last_name_idx as the possible key and key used, the type column says "ref", and it says it's searching only one row, yet it doesn't say "Using index condition" like it does when Andrew does it.

I'm using MySQL workbench on a localhost with MySQL 5.5, but I created a simple table on my web server (with version 5.5 installed), and added an index in the same manner, but got the same result of "Using where".

Thanks for your help!

Kyle

Chris Buczkowski
Chris Buczkowski
3,939 Points

I have this same issue. I wonder if it may be due to the fact that the MySQL version I'm using is more up-to-date than when the videos were made. I'm guessing this isn't too big an issue as it still shows it's only looking through one row, so I know the index is working.

3 Answers

Armands Lazdiņš
Armands Lazdiņš
721 Points

Just found this on dev.mysql.com

"A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column."

Seems like when full table scan is performed, Extra tab will show "Using where" instead of "Using index". But don't worry, it will still use indexes as helper.

If you replace *(wildcard) with last_name in SELECT query, you will get both "Using where" and "Using index".

I have this same "problem"

Vinny Harris-Riviello
Vinny Harris-Riviello
11,898 Points

My as well and it is checking ALL instead of ref ??? but the index key is created, but it is not being used?