patternsqlMinor
MYSQL: What is the benefit of compound indexes, over separate ones?
Viewed 0 times
compoundthewhatbenefitindexesseparatemysqlonesover
Problem
I've been experimenting with the performance of SELECT queries on a table with about a million records, consisting of these columns(among others..):
Wehn i perform this query:
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?
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?
Solution
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
(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.)Context
StackExchange Database Administrators Q#262672, answer score: 4
Revisions (0)
No revisions yet.