I want to put an index on two columns to improve my query performance for each. I also want to perform queries where both columns are used.
From my understanding, if you had a BTREE index on each column, you could theoretically get the same performance by querying both indices and then combining the range you got - if that makes sense?
SELECT * FROM table WHERE f_name = 'first' and l_name = 'last'
Instead checking an index for
('first', 'last') you'd check the
f_name index for
first and the
l_name index for
last. Now, you can check if the results of both index queries are the same (the result of the initial query). Obviously, this is slightly slower than a composite index, but still much faster than no index.
With this idea, you get performance benefits not only when querying both columns together, but either column independently.
My main question is: if I have these 2 independent indices, would I need another index on both, to improve the performance of queries using both columns?