How Mysql Query Plan is generated? Mysql before executing a query with multiple joins. It creates multiple query plan and find the lowest cost plan to execute the query(approx).

My questions are


How the query plan is generated on what basis(like no of fields in the where condition for a particular table)
Is there any formula(or steps) to generate query plan by ourself
The answer is evolving.  More "cost" analysis went into 5.7, still more into 8.0.  There is more to go, such as "histograms" instead of a single number for cardinality.

In the case of joins, MySQL has always pursued all permutations of ordering for the tables.  However, it has an aggressive pruning strategy that keeps it from actually doing "all".  (Key-value (EAV) stores defeat this, but there is a tunable to help.)

The main statistic used is an estimate of the cardinality for each index.  This is found by 'randomly' probing the BTree for the index.  These probes and the retention of the data was significantly improved in 5.6.

Generally, when you are joining multiple tables, the WHERE clause has a lot of sway in saying which table to start with.  If there is no WHERE, then the "smallest" table is picked as the first.  (This is sometimes not the best.)

There are a few tricks to guide the Optimizer.  These are generally a bad idea.  What helps today may hurt tomorrow.

STRAIGHT_JOIN -- do the joins in the order given.
USE/IGNORE INDEX -- give preference to/against specified index(es).
FORCE INDEX -- If at all possible, use this index.

And 8.0 has more.

And there are tricks.  A 'derived' table is allow to ignore ORDER BY.  But you can trick it into doing the sort by adding a LIMIT with a huge value.