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'
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.