0

Let's say I have a compound index involving these 3 columns.

(name, email, phone)

Is this index still will be used for these queries? The first query involves just 2 of indexed fields and the second index using more then all of the fields. By the way, which index type is most suitable for this?

SELECT * FROM person WHERE emails = 'xxx@email.com' AND phone = '012645678'

and

SELECT * FROM person WHERE name = 'dude' AND emails = 'xxx@email.com' AND phone = '012645678' AND title = 'CEO'
gozluklu_marti
  • 79
  • 1
  • 7
  • 26

1 Answers1

1

For a typical b-tree index should be used for the second query (perhaps depending on table statistics). In general, indexes are used from "left to right", with equality conditions in the where. The first query has no condition on name.

No single index is optimal for both queries, but a an index on (emails, name) would probably do well.

Note also that phone numbers are usually stored as strings. Your comparison is to a number. Type conversions can affect the use of indexes.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So, if we lave less fields (ex. 1) in query then none of possible indexes which has more fields in it helps? – gozluklu_marti Oct 11 '18 at 12:07
  • @Yonetmen . . . The index is used from left to right. To use that index, you want a condition on `name`. – Gordon Linoff Oct 11 '18 at 12:15
  • But my understanding from documentation is that effectiveness is the same regardless of which index column(s) the query conditions use for GIN and BRIN index types. Did I interpreted wrong? – gozluklu_marti Oct 11 '18 at 12:24
  • 1
    @Yonetmen . . . For these types of queries, you would normally use a b-tree index. GIN indexes are usually associated with full-text searches, and BRIN indexes are also very specific. – Gordon Linoff Oct 11 '18 at 12:40
  • Thank you for clarification! – gozluklu_marti Oct 11 '18 at 12:47