How does mysql optimiser chooses a composite Index 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)

Observations

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

INDEX(affiliate_id, funnel_id, created timestamp) handles queries 1,3,4, but not 2.  It is slightly suboptimal for 3 and 1 because it is bulkier than INDEX(affiliate_id, funnel_id) or INDEX(affiliate_id)
Depending on how/when you added the FKs, MySQL is sometimes smart enough to not add a new index because it can see that an adequate one exists.  Do SHOW CREATE TABLE to see the collection of indexes that exist now.  If you see some redundant indexes, DROP them.
If you are creating the table fresh, then this order might be best:

Create this table with INDEX(affiliate_id, funnel_id, created timestamp) and INDEX(funnel_id).  Create other tables and their optimal indexes.
Then add the FKs.
Get rid of any shorter indexes which are a prefix of a longer one.  The FKs should remain happy that there is some index starting with the column it needs to check.

If you provide CREATE TABLEs for the various tables, plus a few INSERTs.  I may play around with it.
Back to your question of what the Optmizer does...
When only x is needed, and given a choice between INDEX(x) and INDEX(x,y), the optimizer will pick the &quot;smaller&quot; index, namely (x).  This is not always optimal.
An example is INDEX(lat), INDEX(lat, long) and WHERE lat BETWEEN ... AND long between ....  In this case the query execution would take advantage of the 2-column index for ICP (Index Condition Pushdown), but it seems to be too dumb to realize it.  It picks the 1-column index and has to work harder to check long.  (This issue may vary with versions of MySQL and MariaDB; there have been a lot of changes in the area.)