CODEWITHSUNDEEP
mysqlsql
i.brod
i.brod

Reputation: 4603

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

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?

Upvotes: 3

Views: 56

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562348

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

Upvotes: 3

Related Questions