CODEWITHSUNDEEP
mysqlindexing
soren.qvist
soren.qvist

Reputation: 7416

Are multiple indices needed for multiple where clauses?

Say I have a query like the following:

SELECT * FROM users WHERE username = 'test' AND somethingelse = 'test'

I'm wondering if it's necessary to index both columns for optimization. Does MySQL first find all username columns with the value 'test', and then search those results for somethingelse columns with 'test'? Or does it happen simultaneously?

Upvotes: 3

Views: 245

Answers (1)

duffymo
duffymo

Reputation: 308968

Yes, you should. Every column and combination of columns that appears in a WHERE clause should have an index for efficient searching.

You get away with it for primary key fields because an index is created for those by virtue of being declared a primary key. Other columns require that you index them.

So you'll have an index for username and somethingelse columns.

If username has to be unique, it might have its own index.

Upvotes: 4

Related Questions