Ok I know the basics about when indexing should be used and the pros and cons of using it.
These are the (only) two ways my queries will look like:
SELECT target_id FROM map WHERE table_name = '' AND fingerprint = '' AND source_id = 1;
and
SELECT fingerprint, source_id FROM map WHERE table_name = '' AND target_id = 1;
I know I have to index fields table_name, fingerprint and source_id going by first query. Also target_id going by the second query. I can do that individually. But I have heard composite keys give better performance if I could index all the fields as one. Since my case is so specific that there wont be another WHERE clause, I thought of creating composite keys like this:
(table_name, fingerprint, source_id)
and
(table_name, target_id)
But since table_name overlaps in both composite indexing, I am unsure if that's the way to go. Are there performance implications with such overlapping?
When I did some testing, I did not get any performance improvements with either, both were equally fast.I have about only few tens of thousands of records right now, may be thats the reason. But data will scale to millions of records. So I thought of getting expert opinion on it. Thanks.
