Are multiple indices needed for multiple where clauses?

Question

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?

Answer 1

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.