Should every MYSQL field, that would be used in a "WHERE" clause- have an index?

user_8340889 0
Viewed 39

I'm wondering whether indexes should be added to various fields in a table, when it's known that those fields would be used in "SELECT...WHERE [field] = ..." queries.

For example, let's say i have a "user" table with the following fields:

 id(primary), userName, firstName, lastName

Assuming i will have a query like "SELECT * FROM user WHERE userName = 'someUser123';", should an index be added to the userName column?

Will this query be faster if an index is present, in a scenario where i have thousands of records?

mysql sql
1 Answers

In general, yes, searching a column is faster if there's an index.

Think of a telephone book. If I ask you to look up everyone whose last name is "Smith" it's very quick because you can assume the book is sorted by last name, and all the "Smiths" are together, and alphabetized.

That's the basic idea of how indexes work. They're sorted, and because of this, a search can be done a lot quicker.

Should you index every column? That's a much more complex question.

You might like my presentation How to Design Indexes, Really.

And the video of me presenting it: https://www.youtube.com/watch?v=ELR7-RdU9XU

Bill_Karwin_208600
Related