MYSQL: What is the benefit of compound indexes, over separate ones

indexMySQL

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?

Best Answer

If you know that you frequently query on a combination of fields, a combined index means "you only have to search one B-tree." Otherwise, the server would have to search n individual B-trees and then combine the results.

(N.B.: A "B-tree" is a data structure commonly used by database servers to create indexes ...)

(You can use the EXPLAIN command to quickly see how the server plans to do this ... and to see that it does have to do this.)

Related Question