Index for date column along with multiple other columns in where clause
This is a "range:  joining_date &gt;= '2017-01-01' AND joining_date &lt;= '2017-05-31'

This is sort of a range:  department_no IN (5,4,7) .  There are cases where it acts efficiently like =, there are cases where it should be considered a "range".

This is an =:  department_no IN (5) .  That is, the Optimizer turns it into department_no = 5, which has much more optimization potential.

http://mysql.rjweb.org/doc.php/index_cookbook_mysql says to put range columns last, not first in an index.

In your queries, it is hard to predict what is optimal.

If you are likely to sometimes have single-item INs, then you need

INDEX(department_no, joining_date) -- in THIS order


Also, newer versions of MySQL may work well with that index for the multi-item IN by leapfrogging across the table.  So add that index.

As for

INDEX(joining_date, ...)


When using a 'range', that index will not get past joining_date to whatever other column(s) follow it.  So you may as say just

INDEX(joining_date)


As for the GROUP BY...  Neither of your queries has any hope of using an index for the GROUP BY.  So there will be some form of extra effort after the WHERE -- a tmp table, a sort, something.


Hiding a column in a function month(joining_date) -- the Optimizer can't use the joining_date column an any index for this.
The WHERE left the results in some messed up order (assuming a multi-item IN)


Unrelated... I like this pattern:

AND joining_date &gt;= '2017-01-01' 
AND joining_date  &lt; '2017-01-01' + INTERVAL 1 MONTH


It avoids computing the end, leap year, DATE vs DATETIME vs DATETIME(6) issues, etc.

Bottom line: Have this one index:

INDEX(branch_name, department_no, joining_date)


Then get the EXPLAIN SELECT ... to see if it did the leapfrogging.  If that fails, let's rethink.