I have a table with the following structure.
-id (bigint|pk) -funnel_id (bigint|fk with funnels table) -affiliate_id (bigint|fk with affiliates table) -created_timestamp (bigint)
- funnel_id has 10,000 unique records
- affiliate_id has 15,000 unique records
I have various combinations of SELECT query on the table like:
1) SELECT * FROM tbl WHERE affiliate_id = 300; 2) SELECT * FROM tbl WHERE funnel_id = 300; 3) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300; 4) SELECT * FROM tbl WHERE affiliate_id = 300 AND funnel_id = 300 AND created_timestamp BETWEEN 600 AND 700;
So as per the rule, I should create a composite index with the below statement. Also, it gives me a (100 score) when I checked with the EXPLAIN keyword on the query.
CREATE INDEX ON tlb index_name(affiliate_id, funnel_id, created_timestamp)
But as soon as I create a foreign_key on funnel_id and on affiliate_id the optimizer starts picking up the foreign_key index and the (100 score) also reduces (to 10).
But then I created a composite index on
(affiliate_id, funnel_id). Guess what happend next. The query optimizer again stated to pick the
(affiliate_id, funnel_id, created_timestamp) index.
So my question here is, why the optimizer didn't pick the
(affiliate_id, funnel_id) index. Guys, please let me know your views on this as it is getting a bit confusing.