I've been experimenting with the performance of SELECT queries on a table with about a million records, consisting of these columns(among others..):
id, lastName, firstName,
Wehn i perform this query:
SELECT * FROM `user` WHERE lastName = 'someLastName' AND firstName = 'someFirstName'
I get more or less the same performance(in terms of miliseconds), whether i have two separate indexes on those fields, or have a compound index(lastName,firstName).
I came across this thread:difference between creating index with two columns and creating separate index on two columns
The guy explains the case where a compound index might fall short, bu he didn't give any example where it can actually be beneficial, over setting up separate indexes.
Can someone provide an example/simple explanation of a scenario where a compound index is a good choice?